Combo Box Drop Down for Excel Worksheet

Would you prefer a bigger font size for items in a data validation drop down list? Would you save typing time if the words were completed automatically, as you started typing them?

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.

comboboxsheet01a

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. There are instructions on my Contextures website, for naming a range of cells.

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

comboboxsheet02

  • 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)

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

  • 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 an address instead, such as B2:B13

comboboxsheet04

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.

_________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

20 comments to Combo Box Drop Down for Excel Worksheet

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

  • Rick Rothstein (MVP - Excel)

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

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

  • marsha

    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.

  • marsha

    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.

  • Rob

    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?

  • [...] of the AutoComplete Feature. The Contextures site has a code example and clear instructions.... Combo Box Drop Down for Excel Worksheet | Contextures Blog That article links to another that shows how to couple the ComboBox with [...]

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

  • Clark Leslie

    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.

  • Clark

    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?

  • Wona

    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.

  • Mike

    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

  • Bill P

    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,

  • Rod

    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.

  • Julie

    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?

  • Joseph

    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
    '====================================

  • Jen

    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?

  • Bruce

    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.

  • Bruce

    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.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>