Search Contextures Sites

 

 

Learn how to create Excel dashboards.

 

 

 

Learn how to create Excel dashboards.

Excel VBA ComboBox Lists

Create a ComboBox  
Single Column ComboBox  
Multiple Column ComboBox  
The Completed Excel VBA Code  
Download a Sample Excel VBA ComboBox file  

Watch the Excel VBA ComboBox video

Create a ComboBox

In an Excel UserForm, you can create drop down lists by using the ComboBox control. In this example, the UserForm has two ComboBox controls, one for Part ID, and one for Location.

A ComboBox can have a single column, like this list of location names.

Excel ComboBox One Column

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

Excel ComboBox Multiple Column

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, and then 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 all the locations from a named range -- LocationList -- on the LookupLists worksheet.

Location List

There is only one column in the 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. To do that, we'll use the Initialize event for the UserForm.

  1. In the Visual Basic Editor (VBE), select the UserForm, and on the Menu bar, click View, then click Code. 
  2. In the dropdown at the top left of the VBE, select UserForm (it may be selected already).
  3. From the Procedure dropdown, at the top right, choose Initialize

    Excel UserForm Code

  4. The Initialize event code is added to the Excel VBA code module, with Sub and End Sub lines only. The cursor is flashing between the first and last line of code.

Initialize Procedure

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.

Part ID List

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. 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.

Watch the Excel VBA ComboBox video

 

Download a Sample Excel VBA ComboBox file

Download the zipped sample Excel UserForm With ComboBox file

Watch the Excel VBA ComboBox video

 
 

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright ©2010
All rights reserved.

 

Last updated: April 6, 2010 0:40 AM