Combo Box Drop Down for Excel Worksheet

Combo Box Drop Down for Excel Worksheet

Would you prefer a bigger font size for items in a data validation drop down list?

Could you save typing time, if the words were completed automatically, as you started typing them?

Data Validation Combo Box

Not all your dreams can come true in Excel, but this tip might fulfill a few of them. Instead of using data validation, you can use a combo box on an Excel worksheet to show a drop down list.

At the left, in the screen shot below, is a combo box, and you can compare it to the data validation drop down list on the right.

Data Validation Combo Box on worksheet
Data Validation Combo Box on worksheet

Combo Box Features

Unlike data validation, the combo box can be formatted and fine tuned. For example, in a combo box, you can:

  • change the font size
  • increase the number of visible rows in the list
  • use the AutoComplete feature to make data entry easier
  • always see the drop down arrow

Create a List

In this example, there is a named range — MonthList — with a list of the 12 month names.

This named range is used in the data validation list, and we will also use it as the source for the combo box.

Add a Combo Box to the Worksheet

To add a combo box on an Excel sheet, follow these steps:

  • On the Excel Ribbon, click the Developer tab (If you don’t see a Developer tab, click here for instructions on how to add it)
  • In the Controls group, click Insert, then click the Combo Box in the ActiveX Controls section
  • Then, click on the worksheet, to create a default sized combo box (or drag with the pointer, to create a combo box in the size that you need)
Add a Combo Box to the Worksheet
Add a Combo Box to the Worksheet

Set the Combo Box Properties

After you create the combo box, follow these steps to change its properties:

  • Right-click on the combo box, and click Properties
  • To change the Font size, click the … button at the right of the Font property

comboboxsheet03

Add Items to Combo Box

To add items to the combo box drop down list, type a range name or range reference in the List Fill box.

In this example, we use the named range, MonthList.

You could use a range address instead, such as B2:B13

comboboxsheet04

Number of Rows in Drop Down

To change the number of visible rows in the drop down, increase or decrease the number in the ListRows property box.

For a list of 12 months, we could change the setting to 12, so all the months can be seen, without scrolling through the list.

comboboxsheet05

Activate the Combo Box

After you’ve made the changes to the combo box properties:

  • Click the Design Mode command on the Excel Ribbon’s Developer tab, to exit Design Mode.

RibbonDesignModeOff

  • Click the drop down arrow on the combo box, to see the list, and click an item to select it.

Use a Combo Box With Data Validation

You can also use a combo box to supplement a data validation list, as described on the Excel Data Validation Combo Box page on the Contextures website.

In that example, there’s one hidden combo box on the worksheet, and it appears if you double-click a data validation cell.

Watch the Excel Combo Box Video

To see the steps for creating an Excel combo box with a drop down list, and changing its formatting, watch this short excel video tutorial.

_________

0 thoughts on “Combo Box Drop Down for Excel Worksheet”

  1. Combo boxes are much easier to use for lists than Data Validation. Great tutorial on how to set up a combo box.

    I’d like to point out to those who can’t find the Developer tab, that it’s not shown by default on the Ribbon. Right click anywhere on the Ribbon, select Customize the Ribbon, click the box beside Developer under Main Tabs, and click OK.

  2. @Gregory,

    Are you perhaps using XL2010? I don’t have XL2010 installed yet, so I can’t check, but the procedure you outlined does not work in XL2007. For that version, it appears that you have to click the Office Button (the big round icon in the upper left corner of Excel’s window), click the Excel Options button on the dialog that appears, select Popular from the lefthand listing on the next dialog box that appears and put a check mark in the “Show Developer tab in the Ribbon” checkbox in the “Top options for working with Excel” panel on the righthand side. And, just to mention it for those using XL2003 and earlier version (that offer VB), you would click View/Toolbars/VisualBasic from the menu bar.

  3. @Rick
    I completely forgot they added new functionality in Excel 2010 to customize the Ribbon, and consequently the right-click toolbar has a new shortcut to “Customize the Ribbon” that’s not available in earlier versions of Excel. Good catch. My bad.

    And to be thorough, in Excel 2011 for Mac the Developer tab has to be turned on as well by using the “Customize Toolbars and Menus…” option from the View – Toolbars menu.

  4. I’m using MS2007 and whatever I type does not save in the ListFillRange, thus I cannot assign the list name to use in the combobox. My combo box is in Workbook1, sheet 2; but my list is in Workbook 1, sheet 1. I have been unsuccessful even using a list in the same sheet. What am I missing? The video is great.
    Thanks.

  5. So I’m able to get the ListFillRange to work by typing in the range of cells within the same worksheet (e.g., J2:J2282), but this still does not solve the issue of a text name for a list. Further, I am trying to set up a data entry form, using the activex combobox so the data entry personnel can use the autocomplete feature. We will have thousands of records to enter and I am looking for a way to use the simplicity of data validation to copy the format to various cells but want to use the autocomplete and list features of the combobox. Additionally, I am using the lookup function to find the value selected in the combobox and return a cost value that is preset. If using the data validation, the lookup function works great, but I cannot figure out how to have this function apply to a combobox. Is there an easier way to tackle this? Thanks for any suggestions/help.

  6. Thank you for an enlightening description of how to use this feature.

    I use the Mac version of Excel and can’t seem to find the way to adjust the Properties of the combo box. Right-click or control-click doesn’t bring up a Properties dialog box.

    Any suggestions?

  7. Any way to publish the combo box to web page? When I tried on Excel 2007 the box appears on html, but it does not show the drop down values on clicking it.

  8. Awsome site. You have helped me out greatly.
    I am fairly green to making these active x combo boxes but have figured out how to work it within one spreedsheet. My question now is how do I select a list fill range from another workbook? I can make it work with the vlookup formula to reference the data I want to retrieve but need to pull the info that is entered into the linked cell from another work book.

  9. Found a way to reference the other work book. Just copied and past part of the vlookup formula. My delema now is if I can reference the other workbook without having it open?

  10. One thing that would help is that most of the combo lists are used in more than one cell and sometimes copied down a column. when I am try to do that and make a choice in one then every single cell changes to the choice made in any one given cell so that the whole column populates the exact same data. I would like to know how to copy this down so that each cell populates a choice individual of the other cells.

  11. Trouble is Vlookup doesn’t recognise “Combo Box’s” even though the numbers are nice and big.
    And “Data Validation” numbers are small as when you use the decrease size of sheet slider, with no way of increasing the font size.
    OMG found this “Spin Box’s” on “youtube”
    http://www.youtube.com/watch?v=AVGqGhRNZRM
    Just put beside your “Data Validation” keep them as they are and put in “Cell” address “b5” or what ever, follow video, OMG amazing better best fantastic.
    Came back special to share with You yes You : )
    This is a great site by the way
    Hope this helps someone out, “pay back” “pay forward” : )
    Thanks Mike
    New Zealand

  12. I have a combobox on a modeless userform (Excel 2010) that is populated from cells in the spreadsheet via the Sub UserForm_Initialize(). When I select an item in the combobox, the Sub ComboBox1_Change() is triggered and my code is executed correctly. My code sets various filters & such in the spreadsheet, depending on which item in the combobox was selected. At that point, I can maneuver around the spreadsheet doing edits, etc.
    There are times when I wish to select the same item in the combobox as previously selected to have the code in the Change Event run again. In this case, the Change event is not triggered since no change was made in the combobox.
    Is there a way to force the Change event to trigger when the same item is selected in the combobox? I would have thought that the Click event would work in this case but it does not. The Click event seems to function the same as the Change event.
    Any help yould be appreciated. Thank you,

  13. This may be simple for some, but I’m scratching my head. What I have is a list of codes and their descriptions. I need to display the code and description for the user, but only store the code in the cell.
    Example:
    code description
    10 New Entry
    20 Old Entry
    30 Delete Entry
    Only want the 10, or 20, or 30 saved when selected.

  14. I have a involved spreadsheet that currently uses drop downs but think a combo box would be better suited. The first drop down is a mfg list the second drop down is the model#. There is current a data validation that narrows the second drop down based on the mfg you select. how do I link the second combo box to the first combo box?

  15. Hello,
    I am using this combobox with this VBA. In the example, only text were used. I am using this combobox with numbers. When I entered the numbers in the combobox drop down, I get this as a text instead of a number. How do I format this?
    Thanks,
    Joseph
    ‘==========================
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
    Cancel As Boolean)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim wsList As Worksheet
    Set ws = ActiveSheet
    Set wsList = Sheets(“Schedule”)
    Set cboTemp = ws.OLEObjects(“TempCombo”)
    On Error Resume Next
    With cboTemp
    ‘clear and hide the combo box
    .ListFillRange = “”
    .LinkedCell = “”
    .Visible = False
    End With
    On Error GoTo errHandler
    If Target.Validation.Type = 3 Then
    ‘if the cell contains a data validation list
    Cancel = True
    Application.EnableEvents = False
    ‘get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) – 1)
    With cboTemp
    ‘show the combobox with the list
    .Visible = True
    .Left = Target.Left
    .Top = Target.Top
    .Width = Target.Width + 5
    .Height = Target.Height + 5
    .ListFillRange = str
    .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    ‘open the drop down list automatically
    Me.TempCombo.DropDown
    End If
    errHandler:
    Application.EnableEvents = True
    Exit Sub
    End Sub
    ‘=========================================
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Application.EnableEvents = False
    Application.ScreenUpdating = True
    If Application.CutCopyMode Then
    ‘allow copying and pasting on the worksheet
    GoTo errHandler
    End If
    Set cboTemp = ws.OLEObjects(“TempCombo”)
    On Error Resume Next
    With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = “”
    .LinkedCell = “”
    .Visible = False
    .Value = “”
    End With
    errHandler:
    Application.EnableEvents = True
    Exit Sub
    End Sub ‘====================================
    ‘Optional code to move to next cell if Tab or Enter are pressed
    ‘from code by Ted Lanham
    ‘***NOTE: if KeyDown causes problems, change to KeyUp
    Private Sub TempCombo_KeyDown(ByVal _
    KeyCode As MSForms.ReturnInteger, _
    ByVal Shift As Integer)
    Select Case KeyCode
    Case 9 ‘Tab
    ActiveCell.Offset(0, 1).Activate
    Case 13 ‘Enter
    ActiveCell.Offset(1, 0).Activate
    Case Else
    ‘do nothing
    End Select
    End Sub
    ‘====================================

  16. I can’t access the properties menu after adding a combo box form control? I can access the properties when I first add it and unprotect the control. Once I click off of the control I can’t get back to properties. This is not the case when I add an active x control. Any suggestion?

  17. Take care when using these kinds of dynamic ActiveX objects and the .LinkedCell gets changed often.
    Running the type of code the Joseph has shown in the Worksheet_BeforeDoubleClick was a bit less stable for me. The .DropDown would often not appear.
    I had better luck using it within Worksheet_SelectionChange
    Joseph’s example did pretty much resolve one of the problems that I was having with my version and incorrect screen updating. When you clicked different cells reasonably rapidly you could leave orphan phantoms of the combobox, with or without the dropdown outlined, in the previously selected cells. Slowing things down a bit, as Joseph does, by first “frobbing” the box to the top-left, unlinking, removing the list, etc and then hiding it before going on to normal processing helped out quite a bit. Now he dropdown only occasionally fails to appear and I have no more phantoms in previously selected cells.

  18. I spoke a bit too soon. The issue with the screen updates was only temporarily resolved. It was likely dependant on how much else was going on at time time… like the vba source code stepping etc. More progress…
    If you find that you are still leaving ghosts around at the previous LinkedCell address when you move a ComboBox around the worksheet via vba code, then you might try bracketing the code that does not appear to work consistently with Application.ScreenUpdating = False and Application.ScreenUpdating = False as in…
    on Error GoTo ErrHandler
    Set OLETemp = ws.OLEObjects(“Test_select”)
    On Error Resume Next
    ‘ FROB away the previous ActiveX object
    With OLETemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = “”
    .LinkedCell = “”
    .Value = “”
    Application.ScreenUpdating = False
    .Visible = False
    Application.ScreenUpdating = True
    End With
    or
    Application.EnableEvents = False
    With OLETemp
    ‘show the combobox with the list appropriate to the column that we are in
    .LinkedCell = “=” + Target.Address
    .ListFillRange = “=” & tmp_list
    .Left = Target.Left
    .Top = Target.Top
    .Width = Target.Width + 15
    .Height = Target.Height + 4
    .Visible = True
    End With
    OLETemp.Activate
    ‘ open the drop down list automatically
    Application.ScreenUpdating = False
    Me.Test_select.DropDown
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    In the first case the application now flawlessly removes the display of the control at the previous cell. In the second case the dropdown appears almost all the time.
    I tried busy loops and calling Sleep in kernel32 to no avail.

  19. Thanks a lot. I was unable to load default values for ActiveX Combo. Now I entered the value for “ListFillRange” as “Calculations!$B$60:$B$66” without quotes and my problem is solved.

    Regards,
    RRPV

  20. Using 2007, I’m able to create the ActiveX Combobox and my drop down works, no problem. What I am unable to do is format the box. The properties that appear are those of the worksheet and not the box. How do I get to the properties of the combobox itself and once there, how do I make it uniform in size throughout my document? Lastly, can I make it where the drop down arrow always shows (no matter whether the box is selected or not)?
    Thanks, Paula

  21. I want to add temporary combo box in all drop down data validation in my worksheet. But this code does not work. It says user defined type not defined.

  22. Hi, I have 10 comboboxes on a sales form I am using and have created a command button to clear the sheet, and I want to clear the comboboxes as well – I used the following in the clear command sub-routine but get a runtime error “-2147467259 (80004005)” error on the first clear line.
    Sheets(“Sales”).ComboBox1.Clear
    Sheets(“Sales”).ComboBox2.Clear
    Sheets(“Sales”).ComboBox3.Clear
    Sheets(“Sales”).ComboBox4.Clear
    Sheets(“Sales”).ComboBox5.Clear
    Sheets(“Sales”).ComboBox6.Clear
    Sheets(“Sales”).ComboBox7.Clear
    Sheets(“Sales”).ComboBox8.Clear
    Sheets(“Sales”).ComboBox9.Clear
    Sheets(“Sales”).ComboBox10.Clear
    Would really appreciate your help on how to clear the 10 comboboxes please.
    Thanks a million
    Graham

  23. Hi,
    Thanks a lot for this explanation, very usefull!
    I have one more question:
    My list is long, and when the autocompletion works the dropdown list displays the selected value at the bottom of the visible list.
    Do you know if it is possible to make it displaying the first matching value at the top?
    Here is an example based on monthes (thus with only 3 visible values) :
    text entered : ju
    displayed list today :
    April
    May
    June
    expected display :
    June
    July
    August
    Thanks again and best regards!
    Pierre

  24. I have a series of data which contains booked and bookable hours for multiple departments. I want to create a graph with both booked and bookable hours for each corresponding dept. Can someone help point me to what this would be titled so I can find a how to video, or an excel workbook I could download and follow along, or easy to follow instructions? I just taught myself how to do this with one set of data, and with two side by side, but haven’t been able to figure out how to do with two. Help appreciated, this element of excel is still pretty new to me so I’m a little rusty.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.