Excel 2010 Bible

Part VI: Programming Excel with VBA

856

4. Select the macro’s name and click Add to add the item to the list on the right.


5. (Optional) To change the icon, click Modify and choose a new image. You can also
   change the Display Name.


6. Click OK to close the Excel Options dialog box. The icon appears on your Quick
   Access toolbar.

More on Creating UserForms

Creating UserForms can make your macros much more versatile. You can create custom com-

mands that display dialog boxes that look exactly like those that Excel uses. This section contains

some additional information to help you develop custom dialog boxes that work like those that are

built in to Excel.

Adding accelerator keys

Custom dialog boxes should not discriminate against those who prefer to use the keyboard rather

than a mouse. All Excel dialog boxes work equally well with a mouse and a keyboard because each

control has an associated accelerator key. The user can press Alt plus the accelerator key to work

with a specific dialog box control.

Adding accelerator keys to your UserForms is a good idea. You do this in the Properties window

by entering a character for the Accelerator property.

The letter that you enter as the accelerator key must be a letter that is contained in the caption of

the object. However, it can be any letter in the text — not necessarily the first letter). You should

make sure that an accelerator key is not duplicated in a UserForm. If you have duplicate accelera-

tor keys, the accelerator key acts on the first control in the tab order of the UserForm. Then, press-

ing the accelerator key again takes you to the next control.

Some controls (such as edit boxes) don’t have a caption property. You can assign an accelerator

key to a label that describes the control. Pressing the accelerator key then activates the next control

in the tab order (which you should ensure is the edit box).

Controlling tab order

The previous section refers to a UserForm’s tab order. When you’re working with a UserForm,

pressing Tab and Shift+Tab cycles through the dialog box’s controls. When you create a UserForm,