Search and Find

By Sections

By Levels

Other Links

VBA Lesson 3-1: Forms (Userforms) in VBA for Excel

The form or userForm is also known as a GUI (Graphical User Interface). The form is used to require values, parameters and information  from the user to feed the VBA procedure. Different basic controls can be added to the userform they are called: Label, TextBox, ComboBox, ListBox, CheckBox, OptionButton, ToggleButton, Frame, CommandButton, TabStrip, MultiPage, ScrollBar, SpinButton and Calendar. You can find all kinds of other controls on the Internet but the problem is that if you send your workbook to other people and the new control is not installed on their computer it wont work. This problem might even occur with the calendar. To learn more about all the controls see lessons 3-3 to 3-10.

Before you start developing a userform to enter data in an Excel database see the data entry form that Excel offers you.

Creating a userForm in VBA for Excel

Creating a form in VBA is quite easy. In the VBE (Visual Basic Editor) you right click anywhere on your project in the Project Window and select "Insert/UserForm" and here is what you see:

In the list of components of your project the userform name appears . Anytime you want to work on your userform you double click on its name in the Project window.

You can close the toolbox by clicking on its "X" and call it back by clicking on the toolbox icon on the toolbar

Testing the Userform

Anytime that you want to see your userform (finished product or work in progress) at work or when you   want to test it select the  userform by clicking in ay part of it that is empty (and not one of the controls), click on the "Run" button on the toolbar .   Your userform will show with Excel in the background. To return to the VB Editor just click on the "X" of the userform.

Adding Controls

The toolbox offers you 15 controls. They are (starting with the most used): Label, TextBox, CommandButton, ComboBox, ListBox, CheckBox, OptionButton, ToggleButton, Frame, TabStrip, MultiPage, ScrollBar, Image, RefEdit and the calendar. To add controls to the form you left click on whatever control that you want in the Toolbox , hold the button down and drag the control on the form. You can now expand the control to the desired size. Once all your controls are on the form you left click on them and in the properties window you can change the properties of the selected control. You can also right click on them and select "Properties" .

Tab Order

Once you have added the controls here is an important feature. The user  can use the mouse to move from one control to the other but he should be able to move from one control to the other by entering a value in one and clicking "Enter" or "Tab" . To make sure that the user moves from a control to the next one you need to set the tab order. To do so, right click on the form itself and select the "Tab Order" item. Follow the instructions. The first control in the list will be the one that is active (flashing cursor)   when the form is activated. Bring the controls that are not to be used by the user (labels) at the end of the list. In section  24 see how to "tab deactivate" single  controls.

Managing Controls

You can move the controls by clicking on them holding and moving them around. You can resize them by selecting them and using the different handles around them. You can copy or cut them by right clicking on them and choosing the right menu item.

Once you have added your controls you might want to align a few of them or resize a few so that they are all the same size. To do so you first need to select many controls at the same time. To do so left click on the form near one of the controls that you want to select. Hold and drag drawing a frame that includes many controls.

When you let go of the button all the controls that are touched by the frame are selected.

Right click on any of the selected controls and this contextual menu appears:

You can then align the controls (7th menu item) or make them the same size (8th menu item).

Discover the userform properties and VBA code used to work with it  in lesson 3-2