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