Search Contextures Sites
Custom Search

 

Excel UserForms for Data Entry

 

30 Excel Functions in 30 Days

 

 

 

 

 

Excel UserForm Combo boxes

On a UserForm, you can make data entry easier by adding combo boxes. These drop down lists show all the valid entries, and autocomplete, when you start typing in them

This tutorial shows some tips for using Combo Boxes.

Introduction to UserForms

Show Column Heads - Named Range

Show Column Heads - Range Address

Set Column Widths

Download the Sample Files

More Tutorials

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.

Excel UserForm Test

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.

named range with headings

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.

combo box properties

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.

first row shown as column heads

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

.column heads for worksheet range

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.

set the column widths

Now the Column Heads line up correctly with the text.

column heads lined up

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.

check column widths

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

Excel UserForms for Data Entry

Basic Excel UserForm

Create an Excel UserForm with Combo Boxes

UserForm TextBox Validation Code

__

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

Last updated: August 23, 2016 3:26 PM