Creating UserForm Templates

You might find that when you design a new UserForm, you tend to add the same controls each time. For example, every UserForm might have two CommandButtons that serve as OK and Cancel buttons. In the previous section, I describe how to create a new control that combines these two (customized) buttons into a single control. Another option is to create your UserForm template and then export it so it can be imported into other projects. An advantage is that the event handler code for the controls is stored with the template.

Start by creating a UserForm that contains all the controls and customizations that you would need to reuse in other projects. Then make sure the UserForm is selected and choose File Export File (or press Ctrl+E). You'll be prompted for a filename. Then, when you start your next project, choose File Import File to load the saved UserForm.

Emulating Excel's Dialog Boxes

The look and feel of Windows dialog boxes differs from program to program. When developing applications for Excel, it's best to try to mimic Excel's dialog box style whenever possible.

In fact, a good way to learn how to create effective dialog boxes is to try to copy one of Excel's dialog boxes down to the smallest detail. For example, make sure that you get all the hot keys defined and be sure that the tab order is the same. To re-create one of Excel's dialog boxes, you need to test it under various circumstances and see how it behaves. I guarantee that your analysis of Excel's dialog boxes will improve your own dialog boxes.

You will find that it's impossible to duplicate some of Excel's dialog boxes.