User Form - Input Example
This demonstration steps through a simple user form input which adds record information into a
worksheet.
The form is a basic design with the emphasis on how to build the form and code it to respond to the
functionality we are after.
Here's what we are going to achieve:
1.
A user form will load from a button on a worksheet.
2.
Users must complete Firstname, Surname and choose a Department (which will
be coded as mandatory fields).
3.
When choosing the Add button it will append to the worksheet (called Data) and
always find the next available blank row to populate.
4.
The form will remain open clearing the values ready for the next record input until
the Close button is clicked.
5.
A private macro (from the standard module) calls the user form (via its worksheet
button).
There are properties and code for the form, two and buttons and drop-down combo box which we
will need to add the form's private module.
The order in creating such a feature should loosely follow these steps:
1.
Create the user form canvas.
2.
Add the controls to the form and set various basic properties (including names).
3.
Add code to the form's controls
4.
Code the interaction to the worksheet (& prepare the worksheet layout too).
5.
Add a macro to call the form and attach to a button on the worksheet.
6.
Test the process!
Create the user form canvas
Add a new blank user form the VBA Project.
In the VBE Editor, select Insert, UserForm.
Add the controls to the form
You need to add the following controls:
1.
Two Command Buttons
2.
Three Labels
3.
Two TextBoxes
4.
One ComboBox
5.
One CheckBox
Place the controls roughly where you would like to use these control and resize the form.
Don't worry about the exact position for now:
Setting the properties to each control
The following controls can be set using the Properties Window (F4 function key).
First single click to select the control (so it has the focus) and then from the properties changes their
settings.
Here's the table for the above controls (and user form itself):
Control
Property
Value
CommandButton1
Name
Caption
cmdAdd
Add
Default
True
Height
20
Width
60
Left
132
Top
114
TabIndex
Name
Caption
cmdClose
Close
Cancel
True
Height
20
Width
60
CommandButton2
Label1
Label2
Label3
TextBox1
TextBox2
ComboBox1
CheckBox1
Left
198
Top
114
TabIndex
Caption
Height
Firstname:
18
Left
12
Top
12
Width
72
Caption
Height
Surname:
18
Left
12
Top
36
Width
72
Caption
Height
Department:
18
Left
12
Top
60
Width
72
Name
Height
txtFName
18
Left
84
Top
12
Width
108
TabIndex
Name
Height
txtSName
18
Left
84
Top
36
Width
108
TabIndex
Name
Height
cboDept
18
Left
84
Top
60
Width
108
TabIndex
Name
Caption
chkManager
Manager
Height
18
UserForm1
Left
84
Top
84
Width
108
TabIndex
Name
Caption
frmDataInput
Data Input Example
Height
162.75
Width
267
Adding code to controls
The next step is start coding the form and it's important that you have at least named the controls
you wish code as it will generate its own event signature.
Starting with the Close button which will simply close and end the user form.
Private Sub cmdClose_Click()
'close the form (itself)
Unload Me
End Sub
Unload Me refers to itself which is quick and easy. To explicitly close a user form, you refer to
actual name of the form. Therefore, using Unload frmDataInput will be the same outcome.
Next, lets add code (run time) to populate the ComboBox control (cboDept) which will dynamically
create four fixed options to choose from.
Private Sub UserForm_Initialize()
Me.cboDept.AddItem "Finance"
Me.cboDept.AddItem "Sales"
Me.cboDept.AddItem "Markerting"
Me.cboDept.AddItem "Human Resources"
Me.txtFName.SetFocus 'position the cursor in this control
End Sub
As the form loads (initialises), it adds four items to the cboDept control and then positions the
cursor in txtFName ready for the user to start keying in data.
You could of course set this in the properties (RowSource) for cboDept instead which refers to
range of cells in a worksheet.
The final piece of code is attached the cmdAdd button control so when users click this event, it will
add the details to the worksheet (Data).
Private Sub cmdAdd_Click()
Dim i As Integer
'position cursor in the correct cell A2.
Range("A2").Select
i = 1 'set as the first ID
'validate first three controls have been entered...
If Me.txtFName.Text = Empty Then 'Firstname
MsgBox "Please enter firstname.", vbExclamation
Me.txtFName.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If
If Me.txtSName.Text = Empty Then 'Surname
MsgBox "Please enter surname.", vbExclamation
Me.txtSName.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If
If Me.cboDept.Text = Empty Then 'Department
MsgBox "Please choose a department.", vbExclamation
Me.cboDept.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If
'if all the above are false (OK) then carry on.
'check to see the next available blank row start at cell A2...
Do Until ActiveCell.Value = Empty
ActiveCell.Offset(1, 0).Select 'move down 1 row
i = i + 1 'keep a count of the ID for later use
Loop
'Populate the new data values
ActiveCell.Value = i 'Next ID
ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(0, 2).Value
ActiveCell.Offset(0, 3).Value
into the 'Data' worksheet.
number
= Me.txtFName.Text 'set col B
= Me.txtSName.Text 'set col C
= Me.cboDept.Text 'set col D
'Is this person the manager?
If Me.chkManager.Value = True Then 'yes
ActiveCell.Offset(0, 4).Value = "Yes" 'Col E
Else
ActiveCell.Offset(0, 4).Value = "No" 'Col E
End If
'Clear down the values ready for the next record entry...
Me.txtFName.Text = Empty
Me.txtSName.Text = Empty
Me.cboDept.Text = Empty
Me.chkManager.Value = False
Me.txtFName.SetFocus 'positions the cursor for next record entry
End Sub
The above should be easy to follow (look at the comments).
We don't have to tell the system which worksheet to be in as it is going to be called from a control
(worksheet button) where the data is held in the same worksheet and then hide this procedure from
the Macros dialog box stopping any other way for this form to be called.
Create a worksheet button
In the worksheet in Excel, click the Developer tab from the Ribbon Bar and the Inserticon to dropdown a list of controls.
Choose the Button control icon from the Forms (section) and draw a button where you wish to place
it (top row, frozen pane area).
In the assigning macro pop-up dialog box, click the New... button to create a module and signature
and add the following code:
Sub Button1_Click()
'load the form
frmDataInput.Show
End Sub
Add the keyword Private before the Sub keyword to hide this from the macro dialog box.
TEST IT OUT!
This is how the form looks as it is called from the worksheet button control from the Dataworksheet.
