Microsoft Office Tutorials and References
In Depth Information
Determining the Most Appropriate User Interface
Chapter 23 describes how to work with shortcut menus using VBA.
Creating shortcut keys
Another user interface option at your disposal is to create custom shortcut keys. Excel lets you
assign a Ctrl key (or Shift+Ctrl key) combination to a macro. When the user presses the key
combination, the macro executes.
Be aware, however, of these two caveats: First, you must make it clear to the user which keys are
active and what they do; second, you need to be careful not to assign a key combination that’s
already used for something else. A key combination that you assign to a macro takes precedence
over the built-in shortcut keys. For example, Ctrl+S is a built-in Excel shortcut key used to save
the current file. If you assign this key combination to a macro, you lose the capability to save the
file with Ctrl+S. Remember that shortcut keys are case-sensitive, so you can use a combination
such as Ctrl+Shift+S.
Creating custom dialog boxes
Anyone who has used a personal computer for any length of time is undoubtedly familiar with
dialog boxes. Consequently, custom Excel dialog boxes can play a major role in the user
interfaces that you design for your applications. Figure 6-2 shows an example of a custom dialog box.
A custom dialog box is known as a UserForm. A UserForm can solicit user input, get a user’s
options or preferences, and direct the flow of your entire application. You create and edit
UserForms in the VBE. The elements that make up a UserForm (buttons, drop-down lists, check
boxes, and so on) are called controls — more specifically, ActiveX controls. Excel provides a
standard assortment of ActiveX controls, and you can also incorporate third-party controls.
After adding a control to a dialog box, you can link it to a worksheet cell so that it doesn’t require
any macros (except a simple macro to display the dialog box). Linking a control to a cell is easy,
but it’s not always the best way to get user input from a dialog box. Most of the time, you want
to develop VBA macros that work with your custom dialog boxes.
I cover UserForms in detail in Part IV.