Userforms

Excel Userforms are VBA-programmable custom forms used to get input from users. Excel offers a broad choice of buttons, boxes, and other options to create powerful forms. 

Userform Module

As we have seen earlier, Userforms can be inserted into the VBA project in the same way we insert modules. Then, the Userform folder shows up in the project explorer and a Userform template in the code window of the VBA editor.


The Userform gets by default the name UserForm1, UserForm2, etc, as we add them. That name can be changed in the properties window. The caption (also UserForm1 by default) can also be changed there. The properties window is especially helpful when working with Userforms because it shows and allows to set all parameters of the Userform at a glance.



The properties window also shows properties for each of the controls added to the form. Those controls show up in the toolbox when clicking anywhere in the Userform. The controls include labels, textboxes, combo or list boxes, command or spin buttons, among other. You can add any control by just dragging it into the form.



Let’s see an example of a Userform with two Labels, a TextBox, a ComboBox, and two buttons, to request user name and department information prior to proceed with a registration macro.



A Userform can be called out from any VBA regular procedure by using the ‘Show’ method of the Userform object (specified by the Userform name). In the example above we have added a procedure called ‘RegistrationForm’ to Module1. Running that procedure shows the Userform.

 Sub RegistrationForm()
     UserForm1.Show
 End Sub



Notice we have changed the caption of the Userform to ‘Registration’. The users can input name, department and submit the form. But for now, the form does nothing because we didn’t write any code yet. Also, the ‘Department’ drop down doesn’t show any values just yet. Let’s see how we do all that.

Userform Events

The Userform code window opens when double-clicking in the form or in any of the controls added. The below picture shows the events applicable to the Userform. The UserForm_Click() event procedure shows up by default (as we clicked the form), but any other Events can be selected.



There are different events available for the different components. We have just seen the Userform events, but there is a different set of events for each of the controls (in some cases very similar or the same). 

The Initialize event of the Userform can be used to set properties or values into Userform fields prior to being shown to the user. We may want to fill in some information in some of the fields of the Userform such as a TextBox or set the list of items for a ListBox or ComboBox. All of that would be part of the UserForm_Initialize() event procedure.

In the example above, we want to populate the ComboBox with available departments to choose. That’s done in the Userform_Initialize() event procedure. There, we can add an array of departments to the ComboBox with the ‘List’ method. Another possibility would be to add each department individually with the ‘AddItem’ method. Note that ComboBox1 is the default name given, but that could be changed to a more relevant name (e.g. DepartmentBox or DepartmentsList) in the properties window.

 Private Sub UserForm_Initialize()
     ComboBox1.List = Array("Finance", "Human Resources", "Marketing", "Production")
 End Sub



User name and department selection are parameters we want to capture and probably store in a variable or write directly into the worksheet. In this example we declare the userName and userDept variables to store the respective values. We will probably want to do that when the user clicks ‘Submit’, so the code should go in the ‘Click’ event of the button object. Then, we use the Value property of the respective boxes. 

 Private Sub CommandButton1_Click()
     Dim userName As String
     Dim userDept As String
     userName = UserForm1.TextBox1.Value
     userDept = UserForm1.ComboBox1.Value
     Unload Me
 End Sub

CommandButton1 is the default name of the first button we added. It is convenient to change that name in the properties window to one more readable or easier to follow. 

The statement ‘Unload Me’ will close the Userform. You can also just hide it with the UserForm1.Hide statement but then it keeps using memory. In this example makes sense to completely close it once the user submits the information. 

The same statement could be applied to the ‘Click’ event for the ‘Cancel’ button (CommandButton2), so that the form closes when user cancels.

 Private Sub CommandButton2_Click()
     Unload Me
 End Sub


 

No comments:

Post a Comment

Popular Posts