How to add a UserForm to aid data entry in Excel

When you need a user-friendly data input process, create an Excel UserForm. Users who are unfamiliar with Excel will find these forms much easier to navigate than a sheet. Besides ease of use, they’re great tools for restricting choices. In this article, I’ll show you how to create a UserForm object, add controls to it, and then use Visual Basic for Applications (VBA) to link the controls to a sheet. You can work with your own data or download the example .xls or .xlsm file.

LEARN MORE: Office 365 Consumer pricing and features

About the data

A UserForm guides data entry, so the first thing you need is a data range. Figure A shows the simple data set we’ll work with. The workbook must be macro-enabled if you’re using version 2007 or later. Click the File tab (or Office button) and choose Save As. Choose a location as necessary. From the Save As Type drop-down menu, choose Excel Macro-Enabled Workbook (*.xlsm).

Figure A

We’ll create a simple UserForm that will help users enter new records into this data set.

Create the UserForm

With a bit of data in a worksheet, you’re ready to move to the Visual Basic Editor (VBE) to create the UserForm:

1. Press [Alt]+[F11] to launch the VBE.
2. Inside the VBE, choose UserForm from the Insert menu (Figure B).
   Figure B
   
3. Press [F4] to display the UserForm’s property sheet and enter a name in the Name control. When you do, the VBE updates the property dialog’s title, accordingly (Figure C).
   Figure C
   

Add controls

The blank UserForm is ready for input controls. We’ll add a few text box, combo box, and command button controls. To add the first text box, do the following:

1. With the UserForm selected, click Text Box in the Toolbox and then drop it onto the form. If the Toolbox isn’t visible, be sure to click the UserForm. It’s visible when the UserForm is selected.
2. With the new text box control selected, name it txtGivenName using the property sheet, which will update for the selected element (Figure D).
   Figure D
   

Using Figure E as a guide, add the additional controls listed in Table A and name them. The labels aren’t necessary on this simple example, but when creating a UserForm for your own data, you’ll probably want to include them. When you do, Excel’s default names are usually adequate. Enter a descriptive caption for each label and the two command buttons. It’s a good idea to save your work as you progress.

SEE: Comparison chart: Office suites (Tech Pro Research)

Figure E

Add the remaining controls.

Table A

Add code

The code behind the form makes it work. Don’t let the amount of code intimidate you. The Save command button does most of the work. Once you enter all the values, the code in this button will transfer the entered values to the sheet. To add code, double-click the UserForm to open its module and enter the procedures in Listing A. (Don’t try to copy and paste from this web page because the VBE will object to some web characters. Instead, download one of the example files: Module1.bas or ufrmAnimals.frm.)

Listing A

Private Sub cboClass_DropButtonClick()
'Populate control.
Me.cboClass.AddItem "Amphibian"
Me.cboClass.AddItem "Bird"
Me.cboClass.AddItem "Fish"
Me.cboClass.AddItem "Mammal"
Me.cboClass.AddItem "Reptile"
End Sub
Private Sub cboConservationStatus_DropButtonClick()
'Populate control.
Me.cboConservationStatus.AddItem "Endangered"
Me.cboConservationStatus.AddItem "Extirpated"
Me.cboConservationStatus.AddItem "Historic"
Me.cboConservationStatus.AddItem "Special concern"
Me.cboConservationStatus.AddItem "Stable"
Me.cboConservationStatus.AddItem "Threatened"
Me.cboConservationStatus.AddItem "WAP"
End Sub
Private Sub cboSex_DropButtonClick()
'Populate control.
Me.cboSex.AddItem "Female"
Me.cboSex.AddItem "Male"
End Sub
Private Sub cmdAdd_Click()
'Copy input values to sheet.
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Animals")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 1).Value = Me.cboClass.Value
.Cells(lRow, 2).Value = Me.txtGivenName.Value
.Cells(lRow, 3).Value = Me.txtTagNumber.Value
.Cells(lRow, 4).Value = Me.txtSpecies.Value
.Cells(lRow, 5).Value = Me.cboSex.Value
.Cells(lRow, 6).Value = Me.cboConservationStatus.Value
.Cells(lRow, 7).Value = Me.txtComment.Value
End With
'Clear input controls.
Me.cboClass.Value = ""
Me.txtGivenName.Value = ""
Me.txtTagNumber.Value = ""
Me.txtSpecies.Value = ""
Me.cboSex.Value = ""
Me.cboConservationStatus.Value = ""
Me.txtComment.Value = ""
End Sub
Private Sub cmdClose_Click()
'Close UserForm.
Unload Me
End Sub

The first three procedures populate the three combo box controls. The fourth procedure, cmdAdd_Click(), copies the input values from the UserForm to the data range in the sheet and then clears the controls so you can enter another record. This way, the user can enter multiple records quickly. The last procedure, cmdClose_Click(), closes the UserForm.

The procedure in Listing B opens the UserForm. In the next section, we’ll add a macro button that calls this procedure to the Quick Access Toolbar (QAT). Don’t add this procedure to the UserForm’s module. Instead, choose Module from the Insert menu and add the short procedure.

Listing B

Sub ShowAnimalsUF()
'Display Animals UserForm.
ufrmAnimals.Show Modal
End Sub

Macro button access

By adding a macro button to the QAT, users will have quick and easy access to UserForm. To add the button, do the following:

1. Click the QAT drop-down and choose More Commands.
2. From the Choose Commands From drop-down, choose Macro.
3. Select ShowAnimalsUF (Figure F) and click Add.
   Figure F
   
4. Click Close.

Use the UserForm

To use the UserForm, click the macro button on the QAT to display it — wasn’t that easy? Use the drop-down lists to choose values for the combo box controls and enter values for the text box controls. Figure G shows a new record in the UserForm.

Figure G

Add a new record.

To copy those values to the worksheet, click the Save Animal button. The code behind the UserForm will copy the values from the UserForm controls to the sheet and clear the controls for the next record, as shown in Figure H. To close the form, click the Close button.

Figure H

Copy the values to the sheet.

Worth noting

None of the code saves the record once it’s copied to the sheet. You must remember to save the workbook to save new records at the sheet level. You can add appropriate code to the UserForm or allow users to do that themselves. In addition, there’s no error-handling or data validation in this bare-bones code. Using a combo box or list box helps you limit choices, but you’ll need additional code to protect the validity of text and number values and to force users to enter values for required fields. As you can see, the example form allows the user to leave fields empty.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. I’m not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.