Home > Macros > UserForms > Combo Boxes Excel UserForm Combo Boxes On a UserForm, add combo boxes to make data entry easier. They show all the valid entries, and autocomplete when you start typing in them. Use the tips below, to enhance the Combo Boxes.

Introduction to UserForms

If you haven't built a UserForm already, it requires some programming, and there are setup instructions here: Create an Excel UserForm with Combo Boxes.

Show Column Heads -- Named Range

If the combo box has only one or two columns, you might not need column headings in the drop down list. But, for multiple columns, or columns that might not be clearly understood, it's best to turn on the headings.

In this example, there is an Excel table, and a named range -- MonthTable -- which is based on that table's data range. The table's heading row is not included in the named range.

In the UserForm, there is a combo box, and its row source is the named range -- MonthTable.

The combo box properties are set to show 3 columns, and ColumnHeads is set to True.

With the ColumnHeads property set to True, the heading row in the table is displayed as the combo box column headings, and can't be selected in the drop down list.

Show Column Heads -- Range Address

If the combo box uses a worksheet address as its Row Source, do not include the heading row in the Row Source.

If you set the column Heads property to True, the drop down list will show the row immediately above the range, as the column heads.

In the screen shot below, the Day combo box uses range J2:L10 as its row source. The Column heads show the headings from J1:L1

Set Column Widths

To ensure that the column heads line up with the column text, and to show the long entries, set the column widths for any multi-column combo box. You might have to experiment, to find the right settings.

Here, the widths are set for 50;50;50 -- this will automatically adjust to include "pt", as soon as you press the Enter key, or leave that property cell.

Now the Column Heads line up correctly with the text.

More room is needed for the Long Desc column, so the column widths are changed to 130;50;50. While in the Visual Basic Editor, you can test the column widths, by selecting the combo box, then clicking the drop down arrow.

Download the Sample Files

1. To see how a user form works, you can download the sample UserForm with Combo boxes file. The zipped file is in xlsm format (Excel 2007 and later), and contains macros. Enable macros to test the code.
2. To see the samples from this tutorial, download the Combo Box Tips file. The zipped file is in xlsm format (Excel 2007 and later), and contains macros. Enable macros to test the code.
   

More Tutorials

Basic Excel UserForm

Create an Excel UserForm with Combo Boxes

UserForm TextBox Validation Code