Download the best tutorial, website and reference tool on Excel

Lesson 11: VBA for Excel Code for UserForms and Controls

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. When the message box and the input box are too limited for this purpose you create a form to which you add controls (command buttons, text boxes, list boxes, combo boxes, labels, pictures and other objects). You have learned how to create forms and aadd controls in lesson 9. It is now time to develop some code.

You will first create code to call the form. In a module you will create the following procedure:
Sub proCityForm()
    frmCity.Show
End Sub

You will then add a text box on one of the worksheets and assign this macro to it. To do so you click on the text box that you have added to the sheet from the "Drawing" toolbar and then again on its margin and when the margin becomes a set of small dots you right click on the margin and in the contextual menu you select "Assign Macro" and you follow the instructions selecting "proCityForm". From then on when you left click on the text box the form comes forward. You can also assign this macro to an icon that you add to a toolbar or a VBA command button that you add to the sheet but I much prefer using text boxes.

To code for the controls you double click on them. Let's work on the "Cancel" command button first. When you double click on it the form disappears and in the code window you will see this:
Private Sub cmbCityCancel_Click()
End Sub

in between the two lines of code you will  write:
cbxCity.Value="Select a City"
frmCity.Hide

because basically what you want to happen when the user clicks on this button is for the form to hide and in case the user has already selected a city you want to erase the value from the combo box.

Now the "Submit" button. Let's say that you want the name of the selected city to be transferred to cell C4 of the sheet shReport when the user clicks on the "Submit" button. Double click on it and in between the two new lines of code write:

shReport.Range("C4").value=cbxCity.value
cbxCity.Value="Select a City"
frmCity.Hide

Finally in the combo box you want to offer to the user a list of city. The VBA code to do such a thing has to be developed in the "Activate" event of the form itself. First enter your list of cities on a sheet called shParameters in cells A1 to A5 for example. Come back to the VBA and double click on the form itself two lines of code will appear:

Private Sub UserForm_Click()
End Sub

Erase them and go to the small drop down list located above the code window to your right and select the event "Activate" two new lines of code appear:

Private Sub UserForm_Activate()
End Sub

Between these two new lines you will write:

shParameters.Activate
cbxCity.ColumnCount = 1
cbxCity.RowSource = "A1:A5"

Download the best tutorial, website and reference tool on Excel