Details required :. Cancel Submit. I would do it like so: - Select all cells - Rightclick, select Format cells How satisfied are you with this reply? AddItem "San Francisco". AddItem "Oakland". AddItem "Italian". AddItem "Chinese". SetFocus End Sub. Value End Sub. Value Cells emptyRow, 2. Value Cells emptyRow, 3. Value Cells emptyRow, 4. Value If DateCheckBox1. Caption If DateCheckBox2. However, you can also modify UserForm and UserForm control properties in a different way:.
The Properties Window is commonly displayed on the lower left corner of the VBE, although this varies. The Properties Window shows the properties of the currently-selected object. If your current selection includes more than 1 object, the Properties Window lists the properties that are common to the selected objects. Alternatively, you can use the drop-down list at the top of the Properties Window to specify the object whose properties you want to see. Objects have different properties, although some properties are commonly-shared by different objects.
Therefore, the appearance of the Properties Window changes depending on the object you select. Additionally, the Properties Window can display the properties of the same object organized per 2 different criteria. You select the organization criteria by choosing 1 of the 2 tabs the Properties Window has:. Regardless of the categorization criteria you use, the properties don't change. The Properties Window lists an object's properties and those properties don't change just because they're listed in a different order.
The Properties Window usually contains 2 columns. The left column lists the name of the properties. The right column shows the current property setting. Therefore, once you've selected the object you want to work with , you can proceed as follows:.
You can modify the properties of several objects at the same time. To do this, select all the objects whose property you want to modify prior to executing the 2-step process I describe above.
When you select several objects at the same time, the Properties Window displays those properties that are common to the selection. Some of the properties of the UserForm are default settings for new controls. In other words, the new controls you add to the UserForm are influenced by that property setting. Therefore, if you modify those default control settings:. Each object has its own properties. These properties generally vary from object to object.
There are, however, some properties that are common to several different objects. In practice, you usually modify just a few properties through the Properties Window. Name is 1 such property that you usually set through the Properties Window.
Therefore, the first UserForm you add is UserForm1. The second is UserForm2. The third is UserForm3. And so on…. This may look familiar. Excel uses similar naming conventions for workbooks, worksheets, and regular VBA modules among others. That same rule applies to the controls within a UserForm. For example, if you're working with Label controls, the first Label is named Label1. The second is Label 2.
This property is important:. Since you may use these names relatively often, you may prefer using names that are more descriptive and meaningful than the default ones. Notice, additionally, that any time you change the Name property of a UserForm or a UserForm control, you may have to go back to your VBA code to update the object references.
To avoid this, you may prefer modifying the names of your UserForms and UserForm controls as soon as possible after creating them and prior to writing your VBA code. Several of the general naming rules, such as those that apply to properties and Sub procedures, are also applicable to UserForms and UserForm controls. The following are the main rules you may want to consider when setting the names of your UserForm and UserForm controls:.
A common naming convention for UserForms and UserForm controls involves adding a control identifier at the beginning of the name. You do this by adding a 3-letter prefix to the control name. You can easily delete controls from a UserForm using either of the following 2 methods:. Once you complete the design of your UserForm, you must create the macros that power it and respond to the user's actions. You can create and assign macros to a UserForm in the following 2 easy steps:.
This section focuses on the macros that you attach or assign to the UserForm. This is different from the macros responsible for displaying the UserForm in the first place.
In other words, when working with UserForms, you deal with at least 2 different types of Sub procedures:. Additional considerations apply to the VBA code you use to close the UserForm, even though this code is commonly attached to the UserForm. I explain how you can close or hide a UserForm further below. When working with UserForms, you create event-handler procedures that are stored within the code module of the appropriate UserForm object.
In other words:. You can toggle back and forth between these 2 windows with any of the 3 following features:. When working with UserForms, your main purpose is usually to allow users to enter information or provide other input for your macros. Therefore, your code must be able to respond to what the user does with the UserForm. You do this by relying on events. From a broad perspective, an event is something that happens while you're working with Excel. In the case of UserForms, these are events that occur within the UserForm itself.
Once the appropriate event occurs, your event-handler procedure is executed. This way, your VBA application can respond to the user's actions. When working with UserForms, your code must respond to both UserForm and control events. The events you can work with vary depending on the UserForm control.
In other words, UserForm controls have different associated events. The general principles that apply to event-handler procedures, a topic I cover in this blog post , are applicable to the event-handler procedures you create to deal with UserForms.
You can get the VBE to enter the appropriate declaration statement for an event-handler procedure in the following 9 easy steps:. If you're working in the UserForm window, you can use an alternative process. In this case, you can get the VBE to enter the declaration statement for the event-handler procedure in the following 2 simple steps:. UserForms and UserForm controls are objects. You generally refer to them by using their Name property.
The Name property is the first in an alphabetically-organized Properties Window. You can see how to, and why, modify the Name property of UserForm controls in a previous section. You can see the UserForm as the parent object of the controls within it. Therefore, the basic control reference structure is as follows:.
However, you usually refer to controls within event-handler procedures that are stored in the UserForm's Code module. Therefore, you can simplify the reference by omitting a reference to the UserForm. In this case, you can refer to a control as follows:. An additional alternative is to use the Me keyword to refer to the UserForm. In this case, the reference structure is as follows:. To use the drop-down box or combo box, click any cell so that the object is not selected.
When you click an item in the drop-down box or combo box, cell G1 is updated to a number that indicates the position in the list of the item selected.
To add a spin button in Excel and later versions, click the Developer tab, click Insert , and then click Spin Button under Form Controls. To add a spinner in Excel and in earlier versions of Excel, click the Spinner button on the Forms toolbar. Click the worksheet location where you want the upper-left corner of the spin button to appear, and then drag the spin button to where you want the lower-right corner of the spin button to be.
In this example, create a spin button that covers cells B2: B3. Right-click the spin button, and then click Format Control. In the Current value box, type 1. This value initializes the spin button so that the INDEX formula will point to the first item in the list. In the Minimum value box, type 1.
This value restricts the top of the spin button to the first item in the list. In the Maximum value box, type This number specifies the maximum number of entries in the list. In the Incremental change box, type 1. This value controls how much the spin button control increments the current value. Click any cell so that the spin button is not selected. When you click the up control or down control on the spin button, cell G1 is updated to a number that indicates the current value of the spin button plus or minus the incremental change of the spin button.
The spin button value will not change if the current value is 1 and you click the down control, or if the current value is 20 and you click the up control. To add a scroll bar in Excel and later versions, click the Developer tab, click Insert , and then click Scroll Bar under Form Controls. To add a scroll bar in Excel and in earlier versions of Excel, click the Scroll Bar button on the Forms toolbar.
Click the worksheet location where you want the upper-left corner of the scroll bar to appear, and then drag the scroll bar to where you want the lower-right corner of the scroll bar to be. In this example, create a scroll bar that covers cells B2:B6 in height and is about one-fourth of the width of the column.
0コメント