How to Use VBA Userforms

VBA window with userform

VBA (Visual Basic for Applications) is the built-in programming language that adds depth and ease-of-use to Microsoft Office macros. They can generate dialogues and forms, test for data integrity and display results for programs like Word and Excel. The userform is a VBA object that represents the dialogue and can contains entry fields, controls and coding to manage the form. You generally must have some programming experience to use this form.

Difficulty:

Moderately Challenging

Instructions

Things You'll Need

• Application with VBA such as Microsoft Office programs

1. Display VBA by choosing the "Tools" menu, then "Macro" and finally, "Visual Basic Editor." The VBA window appears.

2. Choose the "Insert" menu and "Userform." A blank userform with handles appears on the right and a "Toolbox" window appears below that. A userform icon appears in the "Project" window in the upper left. The "Properties" window in the lower, left changes to display the userform properties. If these windows are missing, choose them from the "View" menu.

3. Resize the userform by clicking on a handle, holding down the left mouse button and dragging the handle.

4. Rename the userform to something more relevant by clicking the name in the "Properties" windows. Type in a new using the form "frm<name>," which follows recommended programming style. Example names include "frmStartup" or "frmMain."

5. Change the caption by clicking on the "Caption" property and entering a new name. Because this name appear at the top of the userform, enter one that is meaningful to the user such as "Sign-in" or "Main."

6. Adjust additional properties as needed by clicking on their values. You can then set new values by typing them in or selecting them from a dropdown. For example, to change the background color, click the "BackColor" property, choose the "Palette" tab and choose a new color.

7. Adding controls

   Add controls from the Toolbox. If this window is not visible, choose the "View" menu and select "Toolbox." Click on the Toolbox control you want to add and drop it into the userform. Note that the information in the "Properties" window changes to relate to the current control. To define the properties of other controls or the userform, choose a new control in the dropdown at the top of the window.

8. Change a control property by clicking on the property and typing a new value or choosing one from the dropdown. Resize controls by their handles, or click on their centers to move them around the form.

9. Entering code

   Enter code for the userform itself by clicking the first "Select Objects" button in the Toolbox and then by double-clicking on any blank part of the form. The code window appears on the right with a blank "Click" subroutine. Enter code as needed or enter a new subroutine by choosing a name from the "Subroutine" dropdown in the upper right. Note that you can enter code for different controls by clicking the "Control" dropdown in the upper left.

10. Return to the userform as needed by double-clicking its icon in the "Project" window. Don't forget to save frequently as you build the form.

11. Test your creation by clicking the "Play" button of the "Standard" toolbar in the top toolbar. If this bar is missing, choose the "View" menu, then "Toolbars" and finally, "Standard."

12. Close the window and return to the Word document by choosing the "File" menu and then the "Close" option.

References

• Creating userforms