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.
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.
- Tip: There are step-by-step 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
- 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)
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
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
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.
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.
- 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.
_________
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.
@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.
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.
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.
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?
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.
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.
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?
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.
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
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,
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.
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?
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
‘====================================
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?
@jen, press the Ctrl key and click on the combo box to select it. Then you should be able to go to the Developer tab, and click Properties.
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.
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.
So ive made a calendar type thing almost exactly the same as this.
https://www.youtube.com/watch?v=yY9AbS_ehb0
But when i type something in to one month, it shows what i typed for all months even when i click the top left box to change months. Is there any way to make it stick only to that month???????
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
Hello Raghavendra. I saw your VBA posts on this blog:
http://www.myengineeringworld.net/2014/05/pdf-search-through-vba.html
I appear to be having the same problem that you had regarding the automation error. Could you email me please as I’m hoping you may have found the solution.
[email protected]
Thanks.
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
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.
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
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
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.
I forgot to add one of the most important part – I want to create the above with a combo list drop down