Program by Levels
This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple
VBA Lesson 3-1: Forms (Userforms) in VBA for Excel
(Levels: Intermediate)
When the message box or the input box are not sufficient anymore to communicate with the user you need to start developing userforms.
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, Frame, CommandButton and SpinButton . 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
In lesson 1-2 you have learned how to add userforms to your workbooks from the project window of the Visual Basic Editor and this is what you see over the code window the userform itself and the toolbox:
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 and you can play with it. 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, Frame, OptionButton and other ones that you will not use often the ToggleButton, TabStrip, MultiPage, ScrollBar, Image and RefEdit. You can find and download all kinds of controls from the Internet but remember that if you use one of these controls and the person who uses your workbook hasn't installed it on his own computer it will not work.
To add controls to the form you left click on whatever control that you want in the Toolbox. You then go to the userform, click and hold the left button of your mouse and stretch the control to the right dimension.
Once all your controls are on the form you left click on each of them and you can change the properties of the selected control in the properties window. Each type of control has tens of properties but only a few ones are important. You will discover these properties for each of the types of controls in lesson 3-3 to 3-10
To add many instances of the same control you double click on it in the toolbox and you do the stretching part on the useform as many times as you want. BUT don't forget to go back to the toolbox and click on the control one more time to deactivate the "multiple controls" functionality. WARNING: if you want to modify the properties of a control before multiplying it you will add a single copy of the control, you will modify its properties in the properties window and then you will use the copy/paste functionality by right clicking on it. Notice that when you do that the caption of the control (what is written on it) and its name (in the properties window) are not the same. Don't get confused when you start working with them.
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 also be able to move from one control to the other by entering a value in one and clicking "Enter"or "Tab" and the focus will be set on the next control where he is supposed to enter a value not on a label. More importantly when the user enters a value in the final control you ant the focus to be on the right command button (Submit) and not on another one like "Close Form".
To make sure that the user moves from one control to the next one in a set order 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 within) when the form is activated. Bring the controls that are not to be used by the user (labels) at the end of the list. For the controls that are not to be used you can also set the "TabStop" property of the individual control to "False" in the properties window.
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 the VBA code to work with it in lesson 3-2 so that among other things when you activate the userform all the list boxes and combo boxes get initialized.
This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple