Adjusting a Control s Properties | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Adjusting a Control's Properties

Every control has a number of properties that determine how the control looks and behaves. You can change a control's properties, as follows :

At design time when you're developing the UserForm. You use the Properties window for this.
During runtime when the UserForm is being displayed for the user . You use VBA instructions to change a control's properties at runtime.

Using the Properties window

In the VBE, the Properties window adjusts to display the properties of the selected item (which can be a control or the UserForm itself). In addition, you can select a control from the drop-down list at the top of the Properties window (see Figure 13-6).

Figure 13-6: Selecting a control (CheckBox3) from the drop-down list at the top of the Properties window.

Note	The Properties window has two tabs. The Alphabetic tab displays the properties for the selected object in alphabetical order. The Categorized tab displays them grouped into logical categories. Both tabs contain the same properties but in a different order.

To change a property, just click it and specify the new property. Some properties can take on a finite number of values, selectable from a list. If so, the Properties window will display a button with a downward-pointing arrow. Click the button, and you'll be able to select the property's value from the list. For example, the TextAlign property can have any of the following values: 1 - fmTextAlignLeft , 2 - fmTextAlignCenter , or 3 - fmTextAlignRight .

A few properties (for example, Font and Picture ) display a small button with an ellipsis when selected. Click the button to display a dialog box associated with the property.

The Image control Picture property is worth mentioning because you can either select a graphic file that contains the image or paste an image from the Clipboard. When pasting an image, first copy it to the Clipboard; then select the Picture property for the Image control and press Ctrl+V to paste the Clipboard contents.

Note	If you select two or more controls at once, the Properties window displays only the properties that are common to the selected controls.

Tip	The UserForm itself has many properties that you can adjust. These properties are then used as defaults for controls that you add to the UserForm. For example, if you change the UserForm `Font` property, all controls added to the UserForm will use that font.

Common properties

Although each control has its own unique set of properties, many controls have some common properties. For example, every control has a Name property and properties that determine its size and position ( Height , Width , Left , and Right ).

If you're going to manipulate a control by using VBA, it's an excellent idea to provide a meaningful name for the control. For example, the first OptionButton that you add to a UserForm has a default name of OptionButton1 . You refer to this object in your code with a statement such as the following:

 OptionButton1.Value = True

But if you give the OptionButton a more meaningful name (such as obLandscape ), you can use a statement such as this one:

 obLandscape.Value = True

Tip	Many people find it helpful to use a name that also identifies the type of object. In the preceding example, I use `ob` as the prefix to identify the fact that this control is an OptionButton.

You can adjust the properties of several controls at once. For example, you might have several OptionButtons that you want left-aligned. You can simply select all the OptionButtons and then change the Left property in the Properties box. All the selected controls will then take on that new Left property value.

Learning more about properties

The best way to learn about the various properties for a control is to use the Help system. Simply click on a property in the Property window and press F1. Figure 13-7 shows an example of the type of help provided for a property.

Figure 13-7: The Help system provides information about each property for every control.

Accommodating keyboard users

Many users prefer to navigate through a dialog box by using the keyboard: The Tab and Shift+Tab keystrokes cycle through the controls, and pressing a hot key (an underlined letter) operates the control. To make sure that your dialog box works properly for keyboard users, you must be mindful of two issues: tab order and accelerator keys.

CHANGING THE TAB ORDER OF CONTROLS

The tab order determines the sequence in which the controls are activated when the user presses Tab or Shift+Tab. It also determines which control has the initial focus. If a user is entering text into a TextBox control, for example, the TextBox has the focus. If the user clicks an OptionButton, the OptionButton has the focus. The control that's first in the tab order has the focus when a dialog box is first displayed.

To set the tab order of your controls, choose View Tab Order. You can also right-click the UserForm and choose Tab Order from the shortcut menu. In either case, Excel displays the Tab Order dialog box, as shown in Figure 13-8. The Tab Order dialog box lists all the controls, the sequence of which corresponds to the order in which controls pass the focus between each other in the UserForm. To move a control, select it and click the arrow keys up or down. You can choose more than one control (click while pressing Shift or Ctrl) and move them all at once.

Figure 13-8: Use the Tab Order dialog box to specify the tab order of the controls.

Alternatively, you can set an individual control's position in the tab order via the Properties window. The first control in the tab order has a TabIndex property of . Changing the TabIndex property for a control may also affect the TabIndex property of other controls. These adjustments are made automatically to ensure that no control has a TabIndex greater than the number of controls. If you want to remove a control from the tab order, set its TabStop property to False .

Note	Some controls, such as `Frame` and `MultiPage` , act as containers for other controls. The controls inside a container have their own tab order. To set the tab order for a group of OptionButtons inside a `Frame` control, select the `Frame` control before you choose the View Tab Order command.

SETTING HOT KEYS

You can assign an accelerator key, or hot key, to most dialog box controls. This allows the user to access the control by pressing Alt+ the hot key. Use the Accelerator property in the Properties window for this purpose.

Tip	Some controls, such as a `TextBox` , don't have an `Accelerator` property because they don't display a caption. You still can allow direct keyboard access to these controls by using a `Label` control. Assign an accelerator key to the Label and put it ahead of the TextBox in the tab order.

Testing a UserForm

You'll usually want to test your UserForm while you're developing it. There are three ways that you can test a UserForm without actually calling it from a VBA procedure:

Choose the Run Run Sub/UserForm command.
Press F5.
Click the Run Sub/UserForm button on the Standard toolbar.

These three techniques all trigger the UserForm's Initialize event. When a dialog box is displayed in this test mode, you can try out the tab order and the accelerator keys.