Menu

Other links

Userform input example

User Form - Input Example

This article steps you 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 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	cmdAdd
	Caption	Add
	Default	True
	Height	20
	Width	60
	Left	132
	Top	114
	TabIndex	4

CommandButton2	Name	cmdClose
	Caption	Close
	Cancel	True
	Height	20
	Width	60
	Left	198
	Top	114
	TabIndex	5

Label1	Caption	Firstname:
	Height	18
	Left	12
	Top	12
	Width	72

Label2	Caption	Surname:
	Height	18
	Left	12
	Top	36
	Width	72

Label3	Caption	Department:
	Height	18
	Left	12
	Top	60
	Width	72

TextBox1	Name	txtFName
	Height	18
	Left	84
	Top	12
	Width	108
	TabIndex	0

TextBox2	Name	txtSName
	Height	18
	Left	84
	Top	36
	Width	108
	TabIndex	1

ComboBox1	Name	cboDept
	Height	18
	Left	84
	Top	60
	Width	108
	TabIndex	2

CheckBox1	Name	chkManager
	Caption	Manager
	Height	18
	Left	84
	Top	84
	Width	108
	TabIndex	3

UserForm1	Name	frmDataInput
	Caption	Data Input Example
	Height	162.75
	Width	267

You can change some of these properties to taste - this is what I'm using in this example.

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 into the 'Data' worksheet.
    ActiveCell.Value = i 'Next ID number
    ActiveCell.Offset(0, 1).Value = Me.txtFName.Text 'set col B
    ActiveCell.Offset(0, 2).Value = Me.txtSName.Text 'set col C
    ActiveCell.Offset(0, 3).Value = 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 Insert icon to drop-down 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 Data worksheet.

Want to teach yourself Access? Free online guide at About Access Databases