How to Open a Form VBA

VBA (Visual Basic for Applications) includes a method to add a form, called a UserForm. This becomes part of a macro that can be loaded and run. The form is usable as a container for various VBA controls, such as Labels, Command Buttons, OptionButtons and many others. With these, you can gather input from the user and write additional code to carry out actions. These forms will work in Microsoft Office software like Excel, Word, PowerPoint and others, although they may function in slightly different ways.

Instructions

1. Open any Microsoft Office program that supports VBA. This example uses a Word document. In the Word menu, click on “Developer” and then “Visual Basic” on the left of the second menu row.

2. Click “Insert” and select “UserForm” on the Visual Basic menu. Both a form labeled “UserForm1” and a toolbox will appear on the screen. Use the drag handles on the form to resize it to fit your needs.

3. Place the controls you will need on the form by clicking them in the toolbox. Resize them on the form with the drag handles. With the basic toolbox you can add Labels, TextBoxes, CheckBoxes, OptionButtons, ListBoxes and ComboBoxes, plus a few additional controls. Add more by right-clicking on the toolbox and selecting “Additional Controls.”

4. Scroll through a very long list of additional controls available to you in the window that opens. To name just a few, you could add these to your form: a Calendar control that displays a monthly calendar, a DatePicker control showing just today’s date, a Slider control to let a user pick something on a proportional basis, scrollbars and even a Web Browser control.

5. Click “Insert” on the VBA Editing screen and select “Procedure.” In the Code window, enter these lines:

   Public Sub OpenForm()
   Load UserForm1
   UserForm1.Show
   End Sub

   Revert to the Word document window now by clicking on the “W” icon at the left of the second row of menu choices. Select “Macros” and the OpenForm macro will be listed. Run this and the VBA form will open.

Tips & Warnings

• Rename the UserForm and macro name to suit your own purposes.

• Modify the appearance of the controls on the form by opening the Properties window (click “View” and “Properties Window” in the VB editor).

• Do not try to use the Record macro function to create a form.

References

• "Mastering VBA;" Guy Hart-Davis; 2005
• MSDN Office Developer Center: Chapter 21---VBA Primer

Resources

• Microsoft: Access 2003 VBA Language Reference
• Microsoft: Primer in Excel VBA