Multi-Column ComboBox Drop Down Menus in Forms

Multiple columns of data within a UserForm ComboBox drop-down menu in Excel.

I'll show you an easy way to do this and a more complex way to do this using VBA.

Sections:

Multi-Column ComboBox from Worksheet Data (Easy Method)

Multi-Column UserForm ComboBox using VBA

Get Data from any Column of the ComboBox

Notes

Multi-Column ComboBox from Worksheet Data (Easy Method)

This is the easiest method, but it also requires that you keep your list of data within the worksheet in Excel.

1. Setup your data in columns like this in the worksheet:
   
   You can have as many columns as you need and you could place them on any worksheet, even hidden ones.
2. Go to the VBA window (Alt + F11) > Double-click the UserForm from the Project Explorer (Ctrl + R) > Click on the ComboBox and look to the Properties Window (F4).
   
3. Change the ColumnCount property to the number of columns that you have.
   
4. Go to the RowSource property and input the range reference of the data for the menu. In this example, it is A9:B11.
   
5. That's it! 

Run the form and you'll see the multi-column ComboBox:

Adjust the Column Width

You may notice that the widths of the columns in the menu are not good, they are too big, and this is also easy to fix.

Look to the ColumnWidths property and set the width for each column of data.

Separate the widths of each column using a semicolon. If you have 2 columns it could be like 20;50 and for three columns 20;50;30 etc.

Here, I entered 20 for the first column and 50 for the second column - Excel will automatically place the "pt" at the end of it once you enter the numbers and run the form.

Play around with the numbers until it fits your data.

Here is what the menu in our form now looks like after adjusting the column widths:

Multi-Column UserForm ComboBox using VBA

You can manage and maintain multi-column lists within the code for a UserForm; however, this can be a lot more confusing than the other method, which was illustrated above.

The code for this goes into the UserForm_Initialize() event inside the code section for the form. (In the Project Explorer, right-click the form and click View Code and then select UserForm from the left drop-down and Initialize from the right drop-down menu.)

The name of the ComboBox is ComboBox1.

First, declare a variable that says how many rows the list will be:

Dim RowValue(2, 1) As Variant

2 is the number or total rows minus 1.

1 is the number of total columns minus 1.

Tell the form how many columns there will be in the ComboBox:

ComboBox1.ColumnCount = 2

Tell the form how wide each column will be:

ComboBox1.ColumnWidths = "20;50"

20 is for the first column.

50 is for the second column.

Each column is separated with a semicolon. Play around with the widths until your data fits nicely.

Add the Data:

'Row 1
RowValue(0, 0) = "1"
RuowVale(0, 1) = "Item 1"

'Row 2
RowValue(1, 0) = "2"
RowValue(1, 1) = "Item 2"

'Row 3
RowValue(2, 0) = "3"
RowValue(2, 1) = "Item 3"

Note: the rows and columns start with zero and not 1!

Here is how to reference the rows and columns:

Row 1 Column 1: RowValue(0, 0) 

Row 1 Column 2: RowValue(0, 1) 

Row 2 Column 1: RowValue(1, 0)

Row 2 Column 2: RowValue(1, 1)

Etc.

Follow the pattern to add more rows and more columns.

Put the Values into the ComboBox:

ComboBox1.List = RowValue

All Code Put Together

'Add items to the ComboBox - Multi-Column

'Declare the array variable.
'Say how many rows there will be.
Dim RowValue(2, 1) As Variant

'Say how many columns there will be
ComboBox1.ColumnCount = 2

'Set Column Widths
ComboBox1.ColumnWidths = "20;50"

'Row 1
RowValue(0, 0) = "1"
RowValue(0, 1) = "Item 1"

'Row 2
RowValue(1, 0) = "2"
RowValue(1, 1) = "Item 2"

'Row 3
RowValue(2, 0) = "3"
RowValue(2, 1) = "Item 3"

'Put the values into the ComboBox
ComboBox1.List = RowValue

Result

Get Data from Any Column of the ComboBox

When you get values from a multi-column ComboBox, you have to choose which data from which column you will get.

ComboBox1.Column(0)

ComboBox1 is the name of the ComboBox.

Column(0) specifies from which column to get the data. Zero (0) is the first column; 1 is the second column; etc.

In the attached file for this tutorial, two buttons have been added to display these values in a message box pop-up window. Column 1 and Column 2

Note: no error-check has been implemented in this example; this means that you will get an error if you click the Column 1 or Column 2 buttons without first making a selection from the ComboBox.

Notes

Multi-column ComboBox controls can be tricky and annoying to maintain. Only use them if you really must use them.

Download the sample file for this tutorial to see these examples in Excel. (In the sample file, the Column 1 and Column 2 buttons will cause an error if you click them before making a selection from the ComboBox; this isn't a big deal; I wanted the examples to be as easy-to-understand as possible and so error-checking code was omitted.)