Excel VBA ComboBox Lists

Create drop down lists in a UserForm, by using the ComboBox control. In this example, there are two ComboBox controls, one for Part ID, and one for Location.

Excel UserForm ComboBox Code

In this video, you'll see the code that fills the items in the combo boxes on the Excel UserForm that was created in this tutorial: Create a UserForm 

The written explanation of the code is shown below the video.

Create a ComboBox

In an Excel UserForm, you can create drop down lists by using the ComboBox control. A ComboBox can have a single column, like this list of location names.

In this example, the UserForm has two ComboBox controls, one for Part ID, and one for Location.

There are also two command buttons on the UserForm:

• Add the Part button
• Close Form button

Or a ComboBox can have multiple columns, like this parts list that shows both the Part ID and a Part Description.

This Excel VBA example is based on the instructions for Creating an Excel UserForm With ComboBoxes.

On that page, the Excel VBA code is listed, but not explained in detail.

In this tutorial, we'll look at how the Excel ComboBox code works.

• First, we'll create VBA code for a single column ComboBox list
• Next, we'll create Excel VBA code for a ComboBox with two columns.

Single Column ComboBox

This Excel UserForm has a ComboBox named cboLocation.

We want this ComboBox to show a dropdown list of all the locations from a named range -- LocationList -- on the LookupLists worksheet.

• There is only one column in the Location list on the worksheet, so we'll only need one column in the ComboBox.
• We'd like the ComboBox lists to be created automatically, when someone opens our UserForm.

Open the Visual Basic Editor

To do add the Combo Box lists automatically, we'll use the Initialize event for the Excel UserForm.

To see the code for the UserForm, follow these steps:

• To open the Visual Basic Editor, use the shortcut keys - Alt + F11

OR

• On the Excel Ribbon, click the Developer tab
  • NOTE: If the Developer tab is not visible, follow the steps here to display it.
• Next, in the Code group, click the Visual Basic command

Add the Initialize Event Code

After you open the Visual Basic Editor (VBE), follow these steps:

• In the Visual Basic Editor (VBE), go to teh Project Explorer windown, where all the open workbooks are listed
• In the Project Explorer list, find the workbook that contains the UserForm.
• In that workbook's objects, find the UserForm name
• Right-click on the UserForm name
• In the pop-up menu, click the View Code command

Code Window

• At the top left of the code window, click the drop down arrow
• In the drop down list, select UserForm (it may be selected already).
• Next, go to the Procedure drop down, at the top right, of the Code window
• From the list, click on Initialize

The Initialize event code is automatically added to the Excel VBA code module, and two lines of code are automatically created:

• Private Sub UerForm_Initialiaze
• End Sub

The cursor is flashing between those two lines of of code.

Define the Variables

Where the cursor is flashing in the Initialize procedure, we'll define two variables to be used in the procedure:

Private Sub UserForm_Initialize()

Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

End Sub

The cLoc variable is a Range object, and we'll use it to refer to a cell in the LocationList range on the worksheet.

The ws variable is a Worksheet object, and we Set it to the LookupLists worksheet, where the Location list is stored.

Add a Loop

Next, we'll add a For Each...Next loop, that will visit each cell in the LocationList range on the LookupLists worksheet.

Private Sub UserForm_Initialize()
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cLoc In ws.Range("LocationList")

Next cLoc

End Sub

Add the With...End With

Next, we'll add a With...End With statement, that refers to the Location ComboBox, which is named cboLocation. This code is on the UserForm module, so Me refers to the UserForm.

For Each cLoc In ws.Range("LocationList")
  With Me.cboLocation
    
  End With
Next cLoc

Add the List Items

Finally, inside the With...End With, we'll put the code to add the list items. The AddItem method adds an item to the ComboBox, and our code tell Excel to use the value from the current cell (cLoc) in the LocationList range.

For Each cLoc In ws.Range("LocationList")
  With Me.cboLocation
    .AddItem cLoc.Value
  End With
Next cLoc

If you test the UserForm with this Initialize code, the Location ComboBox will have a single column drop down, showing all four locations from the LocationList range. The Part ComboBox will have an empty drop down list, because we haven't added any items to it yet.

Multiple Column ComboBox

Next, we'll add items to the Part ComboBox, which is named cboPart. It will show the Part IDs from a named range -- PartIDList -- on the LookupLists worksheet.

There are two columns in the Parts list on the worksheet, so we'll need two columns in the ComboBox, with Part ID in the first column, and Part Description in the second column.

Add a Variable

At the top of the Initialize procedure, we'll add another variable, for the cells in the Part ID list on the worksheet.

Private Sub UserForm_Initialize()


Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

The cPart variable is a Range object, and we'll use it to refer to a cell in the PartIDList range on the worksheet.

Add a Loop

Next, we'll add a For Each...Next loop, that will visit each cell in the PartIDList range on the LookupLists worksheet.

Private Sub UserForm_Initialize()
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cPart In ws.Range("PartIDList")

Next cPart

Add the With...End With

Next, we'll add a With...End With statement, that refers to the Part ComboBox, which is named cboPart. This code is on the UserForm module, so Me refers to the UserForm.

For Each cPart In ws.Range("PartIDList")
  With Me.cboPart
    
  End With
Next cPart

Add the List Items

Next, inside the With...End With, we'll put the code to add the list items. The AddItem method adds a row to the ComboBox, with the value from the current cell (cPart) in the PartIDList range in the first column of the drop down.

For Each cPart In ws.Range("PartIDList")
  With Me.cboPart
    .AddItem cPart.Value
  End With
Next cPart

Add the Second Column Values

Next, below the AddItem code, we'll put the code to add the values in the second column, using the List property. Our code will tell Excel which row and column of the drop down to use. The ListCount property is the number of items in the drop down list.

For the List property, both the Row and Column counts start at zero. So, if there is 1 item in the drop down list, it is in Row 0. That's why we subtract 1 from the ListCount, to get the Row number.

We want our Part Description in the second column. The first column is 0, so the second column is Column 1.

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

The cPart cell is in column A, and we want the Part Description from column B in the same row. So, we use the Offset property to get the value that is 0 rows down, and 1 column to the right.

Change the ComboBox Properties

For ComboBoxes with multiple columns, change the ComboBox properties, to increase the number of columns, and set the column widths.

Note: You can also show column headings, if you'd like.

The Completed Excel VBA Code

Here's the completed Excel VBA code for the UserForm Initialize procedure. It adds the single column list in the Location ComboBox, and the two column list in the Part ComboBox.

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

At the end, the Excel VBA code puts the current date in the Date text box, and the number 1 in the Quantity text box.

The SetFocus method moves the cursor to the Part ComboBox, so it's ready for the user to select a part.

Get the Sample File

Download the zipped sample Excel UserForm With ComboBox file

Watch the Excel VBA ComboBox video