Create A Userform Template

If you find that you are consistently creating the same type UserForm for displaying custom dialog boxes with your macros, you can create a UserForm template file to save time and effort. When you create UserForms the Visual Basic Editor attaches them to the project where you create them. Each time you create a new project you need to re-create the UserForm or copy it from another project using the Project window. See Chapter 2 for more information on working with the Project window.

When you create a UserForm for use as a template, consider keeping it fairly generic so that you can customize it for each new project. For example, if you frequently create a UserForm with a TextBox control for gathering user input and two CommandButton controls, OK and Cancel, you can create a generic version with the three controls. However, if you do not place the Label control for the text box on the template version, you can import the form and customize it for the type of data you want to gather from the user.

When you create a UserForm template, you design a basic UserForm, and save it to a file. You can then add the form to any other project you create. You can save a UserForm to a file via the Export File command on the File menu. This displays the Export File dialog where you specify the name and location for saving the form file. You may consider creating a folder that you can use for saving any common Excel project files.

You add a UserForm template to a project by using the Import option. When you import a UserForm into your project, the Visual Basic Editor creates a new UserForm and assigns it the next sequential name.

CREATE A USERFORM TEMPLATE

CREATE A USERFORM TEMPLATE

Excel Userform Templates

CREATE A NEW FORM

Q Click File O Export File.

em Insert Format Debug Run

em Insert Format Debug Run

- SampleOialogBox2 (UieiFo

- SampleOialogBox2 (UieiFo

Bj Sheet3 (5heet3) LQ ThisWorkbook B-Q Forms

-H SampleDialogBox El SampleDialogBox2 -m UserFormI El-€¡9- Modules t Module 1 S Module 14 Î Module 15

Bj Sheet3 (5heet3) LQ ThisWorkbook B-Q Forms

-H SampleDialogBox El SampleDialogBox2 -m UserFormI El-€¡9- Modules t Module 1 S Module 14 Î Module 15

CommandBut Alphabetic | Ca

CommandButtoi^

(Name) Accelerator

Comma ndButto_*|

□ &H8000000—'

BackStyle Cancel

1 - fmBackStyle

ControlTipText Default

False

CREATE A NEW FORM

Note: See the section "Create a Custom Dialog Box" for more information on creating UserForms.

Q Click File O Export File.

■ The Export File dialog box displays.

L-H ClickQ and click the location where you want to save the UserForm in the Save in list box.

□ Type the form file name in the File Name field.

Click Save.

CUSTOMIZING DIALOG BOXES, MENUS, AND TOOLBARS

You can specify the order that Excel uses to move between controls on the UserForm by setting the controls' tab order on the UserForm. The tab order indicates the order in which the Visual Basic Editor selects the controls when a user presses the Tab key. By default, the tab order is the order in which you added the control to the UserForm.

Each control has two properties that deal with the tab order. The first property, TabStop, determines whether focus stops on the control when the user presses the Tab key. If you set the property to False for the control, tabbing through controls on the dialog box skips over the control.

The other property, TabIndex, specifies a value between 0 and the number of controls indicating the tab order for the control.

You can set the tab order for the entire list of the controls on the form via the Tab Order dialog box. This dialog box displays when you right-click the UserForm and click the Tab Order option. You can change the order of the controls by clicking a control and then clicking 0 or 0.

IMPORT A USERFORM TEMPLATE_

0 Click File O Import File

■ The Import File dialog box displays.

IMPORT A USERFORM TEMPLATE_

'—n In the Project window, click the project where you want to add the UserForm.

0 Click File O Import File

■ The Import File dialog box displays.

—0 Click the file containing the UserForm.

■ The Visual Basic Editor adds the selected UserForm to the current project.

Was this article helpful?

+10 -13

Responses

Post a comment