Home > Macros > UserForms > Comboboxes
Excel UserForm With Comboboxes
To make it easier for users to enter data in a workbook, you can
create an Excel UserForm. In this example, combo boxes are added,
to create drop down lists where you can select items, instead of typing them.

|
Introduction to UserForms
To create a UserForm requires some programming, and you can see the
steps in the videos shown below. For the written instructions, look
below the videos.
In this example, inventory data is stored on a hidden worksheet,
where it is protected from accidental damage or deletion. Users enter
inventory data by opening the UserForm, filling in the boxes, and
clicking a button.

Videos: Create a User Form
To see the steps for creating a UserForm, please watch these 3 short
video tutorials. The written instructions are below the video.
In the videos, Excel 2003 is used, and the same steps can be followed
in later versions. When saving the file in Excel 2007 or later, save
as a macro-enable file type.
Creating a UserForm - Part 1
In part 1, you'll see how to create a blank Userform.
Then you'll name the UserForm, and next you'll add text boxes and
labels.
Users will be able to type data into the text boxes.
Labels are added beside the text boxes, to describe what users should
enter into the text box
Creating a UserForm - Part 2
In Part 2, you'll learn how to add buttons and a title on the UserForm.
With buttons on the UserForm, a user can click to make something
happen. For example, click a button after entering data in the text
boxes, when you're ready to move the data to the worksheet storage
area.
Creating a UserForm - Part 3
In Part 3, you'll learn how to add VBA code to the controls, and
you'll see how to test the UserForm.
The VBA code runs when a specific event occurs, such as clicking
a button, or entering a combo box. In this example, the user will
click a button, and the VBA code will move the data to the worksheet
storage area.
Creating a UserForm - Part 4
In Part 4, you'll see the code that fills the items in the combo
boxes.
Set up the worksheet
In this example, a parts inventory is stored on a hidden worksheet.
- Open a new workbook
- Double-click on the sheet tab for Sheet1
- Type: PartsData
- Press the Enter key
- In cells A1:D1, enter the headings for the parts inventory database,
as shown at right.
- Choose File | Save, and save the workbook. In this example, the
file has been named PartsLocDB.xls.
NOTE: In Excel 2007 and later versions, save the file as a macro-enabled
file type.
|
A
|
B
|
C
|
D
|
1
|
PartID
|
Location
|
Date
|
Qty |
2
|
12345
|
Store 001
|
3/3/2015
|
87
|
3
|
|
|
|
|
Create a UserForm
UserForms are created in the Visual Basic Editor.
- To open the Visual Basic Editor, hold the Alt key, and press the
F11 key
- Choose View | Project Explorer, to see a list of projects. (Usually,
this is displayed at the left side of the VBE window.)
- In the Project Explorer, select the PartLocDB project.
- From the menu bar, choose Insert | UserForm

- A blank UserForm appears, and the ToolBox should open. (If the
ToolBox doesn't appear, choose View | Toolbox)

Name the UserForm
- To open the Properties window, press the F4 key
- In the Properties window, double-click on the Name -- UserForm1,
at the top right of the window.
- Type: frmPartLoc
and press the Enter key

- The form name will change in the Project Explorer, but the form
still shows UserForm1 in its title bar.
- In the Properties window, double-click on the Caption property--
UserForm1.
- Type: Parts Inventory
and press the Enter key
- The title bar will display the new caption.

Add a Combobox to the UserForm
The objects on a UserForm, such as buttons, and textboxes, are called
controls. To allow users to enter data, you can add textbox
controls to the Excel UserForm, with label controls to describe them.
- In the Toolbox, click on the ComboBox button.

- On the UserForm, click near the top centre, to add a standard
sized combobox.
- With the new combobox selected, double-click on the Name property
in the Properties window.
- Type: cboPart
and press the Enter key
- (optional) Set the ColumnCount property to 2, if you want to see
the part name listed with each part number.
- Click on an empty part of the Excel UserForm, to select the Excel
UserForm and to display the Toolbox.
Add a Label to the UserForm
To help users enter data, you can add labelS to describe the controls,
or to display instructions.
- In the Toolbox, click on the Label button.

- On the UserForm, click to the left of the combobox, to add a standard
sized label.

- With the new label selected, double-click on the Caption property
in the Properties window.
- Type: Part
and press the Enter key
- If necessary, you can resize the label, so it doesn't cover the
combobox -- point to the handle on its right border, and drag to
the left.
- Click on an empty part of the UserForm, to select the UserForm
and to display the Toolbox.
Add remaining boxes and labels
Repeat the above steps to add:
- a combobox named cboLocation, with a label Location
- a textbox named txtDate, with a label Date
- a textbox named txtQty, with a label Quantity

If the textboxes are not aligned, you can align them:
- Click on the first combobox
- Hold the Ctrl key, and click on the remaining boxes
- Choose Format | Align | Lefts
- Click on an empty part of the UserForm, to select the UserForm
and to display the Toolbox.

Add Buttons to the UserForm
To allow users to perform an action, you can add command buttons
to the user form. This form has a button to add data to the database,
and a button to close the form.
- In the Toolbox, click on the CommandButton button.

- On the UserForm, click at the bottom left, to add a standard sized
CommandButton.
- With the new CommandButton selected, double-click on the Name
property in the Properties window.
- Type: cmdAdd
and press the Enter key
- With the new CommandButton selected, double-click on the Caption
property in the Properties window.
- Type: Add this part
and press the Enter key
- Click on an empty part of the UserForm, to select the UserForm
and to display the Toolbox.
- Repeat the above steps to add a CommandButton named cmdClose,
with a label Close

- If required, you can reposition the buttons by dragging them to
a new location on the UserForm.
Add code to the buttons
To make the buttons perform an action, you create code that runs
when the button is clicked.
Add code to the cmdAdd button
- Select the cmdAdd button
- On the Menu bar, choose View | Code.
- This creates a procedure, where you can add your code.

- Where the cursor is flashing, enter the following code:
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")
'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
lPart = Me.cboPart.ListIndex
'check for a part number
If Trim(Me.cboPart.Value) = "" Then
Me.cboPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(lRow, 1).Value = Me.cboPart.Value
.Cells(lRow, 2).Value = Me.cboPart.List(lPart, 1)
.Cells(lRow, 3).Value = Me.cboLocation.Value
.Cells(lRow, 4).Value = Me.txtDate.Value
.Cells(lRow, 5).Value = Me.txtQty.Value
' .Protect Password:="password"
End With
'clear the data
Me.cboPart.Value = ""
Me.cboLocation.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboPart.SetFocus
End Sub
- On the Menu bar, choose View | Object, to return to the UserForm.
Add code to the cmdClose button
- Select the cmdClose button
- On the Menu bar, choose View | Code.
- Where the cursor is flashing, enter the following code:
Private Sub cmdClose_Click()
Unload Me
End Sub
- On the Menu bar, choose View | Object, to return to the UserForm.
To allow users to close the form by pressing the Esc key:
- Select the cmdClose button
- In the Properties window, change the Cancel property to True

To prevent users from closing the form by clicking the X button
When the UserForm is opened, there is an X at the top right. In addition
to using the Close Form button, people will be able to close the form
by using the X. If you want to prevent that, follow these steps.
- Right-click on an empty part of the UserForm
- Choose View | Code
- From the Procedure dropdown, at the top right, choose QueryClose
- Where the cursor is flashing, paste the highlighted code from
the following sample
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close Form button!"
End If
End Sub
- On the Menu bar, choose View | Object, to return to the UserForm.
Now, if someone clicks the X in the UserForm, they'll see your message.

Add Lists to ComboBoxes
To populate the combobox lists, dynamic
named ranges will be created in the workbook. Then, code will
add the range items to the comboboxes, when the UserForm is initialized.
Create Dynamic Named Ranges
- In Excel, insert a worksheet named LookupLists
- In columns A and B, enter Part IDs and Parts, as shown at right,
and in column E, enter a list of Locations.

- Choose Insert | Name | Define
- In the Name box, type: PartIDList
- In the Refers to box, type:
=OFFSET(LookupLists!$A$2,0,0,COUNTA(LookupLists!$A:$A)-1,1)
- Click Add
- In the Name box, type: PartsLookup
- In the Refers to box, type:
=OFFSET(PartIDList,0,0,,2)
- Click Add
- In the Name box, type: LocationList
- In the Refers to box, type:
=OFFSET(LookupLists!$E$2,0,0,COUNTA(LookupLists!$E:$E)-1,1)
Add code to create the lists 
To create the drop down lists, you can loop through a list on the
worksheet, as described below. Or, enter the list's range name in
the combo box properties, as described on the Excel
VBA ComboBox Match page.
- In the VBE, select the UserForm, and choose View | Code.
- From the Procedure dropdown, at the top right, choose Initialize

- Enter the following code. For detailed instructions on this code,
see Excel VBA ComboBox Lists
Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")
For Each cPart In ws.Range("PartIDList")
With Me.cboPart
.AddItem cPart.Value
.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End With
Next cPart
For Each cLoc In ws.Range("LocationList")
With Me.cboLocation
.AddItem cLoc.Value
End With
Next cLoc
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboPart.SetFocus
End Sub
Test the UserForm
To test the form, you can run it from the VBE.
- Click on an empty part of the UserForm, to select the UserForm
and to display the Toolbox.
- On the Menu bar, choose Run | Run Sub/UserForm.

- In the Part textbox, click the arrow, and select a Part ID
- Press the tab key to move to the next control.
- When the boxes have been filled in, click the 'Add this part'
button.
- Click the 'Close form' button, to return to the VBE.
If the tab order was incorrect (e.g. when you pressed the tab key
you moved to the wrong textbox or button), you can change it
- Right-click on an empty part of the UserForm
- Choose Tab Order
- Select a control in the list, and click the Move Up or
Move Down button
- Click OK
Create a Button to open the UserForm
To make it easy for users to open the UserForm, you can add a button
to a worksheet.
- Switch to Excel, and activate the PartLocDB.xls workbook
- Double-click on the sheet tab for Sheet2
- Type: Parts Data Entry
- Press the Enter key
- On the Drawing toolbar, click on the Rectangle tool (In Excel
2007 / 2010, use a shape from the Insert tab)
- In the centre of the worksheet, draw a rectangle, and format as
desired.
- With the rectangle selected, type:
Click here to add Part Information

- Right-click on the rectangle border, and choose 'Assign Macro'
- Click the New button
- Where the cursor is flashing, type: frmPartLoc.Show
- Go to the Excel window, and click the button, to open the UserForm.
- NOTE: While the UserForm is open, you won't be able
to perform any other actions in Excel, such as entering data
on the worksheet. You'll have to close the form first.
- If you want users to be able to perform other actions in
Excel while the form is open, change the above line to: frmPartLoc.Show
False to turn off the Modal setting
Create a Pivot Table
To summarize the data in the parts inventory, you can create a pivot
table.
- Switch to Excel, and activate the PartLocDBCombo.xls workbook
- Select the PartsData sheet.
- Create a dynamic range named PartsDatabase, with a Refers to formula:
=OFFSET(PartsData!$A$1,0,0,COUNTA(PartsData!$A:$A),5)
- From the menu bar, choose Data | PivotTable and PivotChart Report
(In Excel 2007 / 2010, use the PivotTable command on the Insert
tab)
- Click Next, and in the Source Data box, type: PartsDatabase
- Click Next, and click the Layout button.
- Drag the PartID button and the Part button to the Row area.
- Drag the Location button to the Column area
- Drag the Qty button to the Data area
- Click the Finish button
- Name the pivot table worksheet, PartsInventory.

Create a Button to View the Pivot Table
To make it easy for users to refresh and view the pivot table, you
can add a button to a worksheet.
- Switch to Excel, and activate the PartLocDB.xls workbook
- Select the Parts Data Entry sheet
- Add a rectangle with the text::
Click here to view Parts Inventory

- Right-click on the rectangle border, and choose 'Assign Macro'
- Click the New button
- Where the cursor is flashing, type the following code:
On Error Resume Next
With Worksheets("PartsInventory")
.Activate
.PivotTables(1).PivotCache.Refresh
End With
Finish the Workbook
To finish the workbook, you can hide the sheet that contains the
database.
- Switch to Excel, and activate the PartLocDB.xls workbook
- Select the PartsData sheet.
- From the menu bar, choose Format | Sheet | Hide (In Excel 2007
/ 2010, use the Format commands on the Home tab)
- Delete all other sheets, except Parts Data Entry
- Click the Click here to add Part Information button, and
enter your data into the database.
- Close and save the workbook
Get the Sample File
- Basic: Download the sample Excel UserForm file
- Enhanced: Download the sample file with an enhanced
Excel UserForm, with comboboxes
Related Tutorials
Create a Basic UserForm
UserForm Dependent ComboBoxes
UserForm, Open Automatically
UserForm ComboBox VBA
Excel UserForm Search Add Edit
UserForm TextBox Validation Code
UserForm with Help Pages