Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically.
Form controls are not as flexible as ActiveX controls but are compatible with earlier versions of Excel. You can find the controls on the developer tab.
Table of Contents
- Create a combobox using vba
- Assign a macro -Â Change event
- Add values to a combobox
- Remove values from a combo box
- Set the default value in a combo box
- Read selected value
- Link selected value
- Change combobox properties
- Populate combox with values from a dynamic named range
- Populate combox with values from a table
- Currently selected combobox
Watch this video about Combo Boxes
Create a combobox using vba
Sub CreateFormControl()
'Worksheets("Sheet1").DropDowns.Add(Left, Top, Width, Height)
Worksheets("Sheet1").DropDowns.Add(0, 0, 100, 15).Name = "Combo Box 1"
End Sub
Recommended article
Recommended articles
In this post I am going to demonstrate two things: How to populate a combobox based on column headers from […]
Assign a macro - Change event
You can assign a macro to a combobox by press with right mouse button oning on the combobox and select "Assign Macro". Select a macro in the list and press ok!
This subroutine does the same thing, it assigns a macro named "Macro1" to "Combo box 1" on sheet 1. Macro1 is rund as soon as the selected value in the combo box is changed.
Sub AssignMacro()
Worksheets("Sheet1").Shapes("Combo Box 1").OnAction = "Macro1"
End Sub
Recommended article
Recommended articles
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
Add values to a combo box
You can add an array of values to a combo box. The list method sets the text entries in a combo box, as an array of strings.
Sub PopulateCombobox1()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.List = _
Worksheets("Sheet1").Range("E1:E3").Value
End Sub
The difference with the ListFillRange property is that the combo box is automatically updated as soon as a value changes in the assigned range. You don´t need to use events or named ranges to automatically refresh the combo box, except if the cell range also changes in size.
Sub PopulateCombobox2()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.ListFillRange = _
"A1:A3"
End Sub
You can also add values one by one.
Sub PopulateCombobox3()
With Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat
.AddItem "Sun"
.AddItem "Moon"
.AddItem "Stars"
End Sub
Recommended article
Recommended articles
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
Remove values from a combo box
RemoveAllItems is self-explanatory.
Sub RemoveAllItems()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.RemoveAllItems
End Sub
The RemoveItem method removes a value using a index number.
Sub RemoveItem()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.RemoveItem 1
End Sub
The first value in the array is removed.
Recommended article
Recommended articles
A dialog box is an excellent alternative to a userform, they are built-in to VBA and can save you time […]
Set the default value in a combo box
The ListIndex property sets the currently selected item using an index number. ListIndex = 1 sets the first value in the array.
Sub ChangeSelectedValue()
With Worksheets("Sheet1").Shapes("Combo Box 1")
.List = Array("Apples", "Androids", "Windows")
.ListIndex = 1
End With
End Sub
Recommended article
Recommended articles
In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]
Read selected value
The ListIndex property can also return the index number of the currently selected item. The list method can also return a value from an array of values, in a combo box. List and Listindex combined returns the selected value.
Sub SelectedValue()
With Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat
MsgBox "ListIndex: " & .ListIndex & vbnewline & "List value:" .List(.ListIndex)
End With
End Sub
Recommended article
Recommended articles
There are two different kinds of text boxes, Form controls and ActiveX Controls. Form controls can only be used on […]
Link selected value
Cell F1 returns the index number of the selected value in combo box 1.
Sub LinkCell()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.LinkedCell = "F1"
End Sub
Cell G1 returns the selected value in combo box 1.
Sub LinkCell2()
With Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat
Worksheets("Sheet1").Range("G1").Value = .List(.ListIndex)
End With
End Sub
Recommended article
Recommended articles
There are two different kinds of text boxes, Form controls and ActiveX Controls. Form controls can only be used on […]
Change combobox properties
Change the number of combo box drop down lines.
Sub ChangeProperties()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.DropDownLines = 2
End Sub
Populate combox with values from a dynamic named range
I created a dynamic named range Rng, the above picture shows how.
Sub PopulateFromDynamicNamedRange()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.ListFillRange = "Rng"
End Sub
You can do the same thing as the subroutine accomplishes by press with right mouse button oning the combo box and then press with left mouse button on "Format Control...". Input Range: Rng
Back to top
Populate combox with values from a table
Sub PopulateFromTable()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.List = _
[Table1[DESC]]
End Sub
Currently selected combobox
It is quite useful to know which combo box the user is currently working with. The subroutine below is assigned to Combo Box 1 and is rund when a value is selected. Application.Caller returns the name of the current combo box.
Sub CurrentCombo() MsgBox Application.Caller End Sub
COMBO BOXES [Form Controls] examples
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
This article demonstrates how to locate a shape in Excel programmatically based on the value stored in the shape. The […]
This article demonstrates how to move a shape, a black arrow in this case, however, you can use whatever shape […]
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]
Excel defined Tables, introduced in Excel 2007, sort, filter and organize data any way you like. You can also format […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article demonstrates how the user can run a macro by press with left mouse button oning on a button, […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
This blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code. The list box […]
There are two different kinds of text boxes, Form controls and ActiveX Controls. Form controls can only be used on […]
Combobox category
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]
In this post I am going to demonstrate two things: How to populate a combobox based on column headers from […]
Form controls category
This article describes how to create a button and place it on an Excel worksheet, then assign a macro to […]
This blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code. The list box […]
In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]
Macro category
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists 1. Save invoice data - […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Vba category
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists 1. Save invoice data - […]
Table of Contents Add Checkboxes to a Worksheet Copy selected rows based on check boxes Worksheet data VBA code Where […]
Excel categories
28 Responses to “Working with COMBO BOXES [Form Controls]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Great Article!....... I noticed that your articles are getting truncated in my Reader. Previously I used to get the complete blog directly into my reader..... Thanks again Oscar!
precisely what I was looking for. very well done.
obie,
Thank you for commenting!
Thanks good sample.
Is there anyway to populate a combo box with a shape in the drop down instead of text?
Sonya,
As far as I know, no!
Excellent tutorial . . .!! simple, clear and usefull
Is there anyway to populate a combobox (ActiveX Control type) with multiple columns from a Access (2010)database table?
Oscar-M,
Is there anyway to populate a combobox (ActiveX Control type) with multiple columns from a Access (2010)database table?
I don´t know, try an Access forum.
[…] Hello See if this page is useful: Working with combo boxes (Form Control) using vba | Get Digital Help - Microsoft Excel resource […]
[…] future issues. If you choose this approach, see quick reference on the VBA excel-form-combobox at: Working with combo boxes (Form Control) using vba | Get Digital Help - Microsoft Excel resource Good luck. Martin […]
Hello, Is there anyway to activate a form control combobox to force an user to choice an item ?
Thanks
Fanch,
Are you talking about a default value? If that is the case, check out:
Set the default value in a combo box
No default value, the user must to be notified to choive a value, the design must expand the list .
is there any way to have more spaces between the values shown in drop down list??
yes create data validation .. select from list .. create a list and write data in alternating column in list
Do you know if it is possible to select multiple items within a combobox list? Similar to Table filter options or by using Ctrl?
Wally
Do you know if it is possible to select multiple items within a combobox list?
No, you need to use a listbox (form control) or a combo box (active x).
Good Tutorial...
this is what I'm looking for
thanks
metha
thanks!
Hi Oscar,
This is very useful and thanks for this post. I need a help here:
I have a range of cells holding values for Emp name, salary, age. Same empname can have multiple rows.
I have a combobox which lists down only unique values (say Emp name) in the dropdown.
I also have a listbox which displays the row of the value selected in the combo box. While doing so, only one row is displayed when the name selected has multiple entries. Could you please help here
Can i ask: Combobox embedded in range a1:a100 with same dropdown list values. Now when user makes selection the selected text goes respectively to selected combobox reference cell. Eg ComboboxA1 txt value goes to cell A1. And same thing with the rest from A2 to A100.
So here's my dilemma: We have an Excel spreadsheet request form that users complete to make requests. One of the columns is currently a drop-down list of vendors that is from a named field. It allows them to pick the vendor on the list they want to purchase from. There are 2000-plus vendors on this list. People say it is taking too long to find their vendor on the list and they want to be able to type in a few letters to select their vendor. I got this working pretty slick with a combo box but my boss says this will make the form too large - that the combo boxes will take too much space. He wants me to use VB code instead, no combo box, to accomplish the same thing. Part of the problem I have is that we have some vendors who have multiple listings on the list - different divisions of the same vendor. They all start with the same first few letters. Without a list, I don't know how they will see the rest of the names so they can pick the right one. Suggestions? I still say the combo box is the way to go.
Hi Oscar - Is is possible to populate the dropdown from a range on another sheet?
The article was really useful
Really well written article, SIMPLE, direct to the focus.
Thanks to people like you!
Dim AppCaller As String
AppCaller = Application.Caller
MsgBox AppCaller
Getting Run time error "13" Type Mismatch on AppCaller statement. Trying to get the name of the active combo box name. Excel v16 windows 10 home.
Hi Oscar,
I was wondering if you can help me out in printing of UserForms. I have a designed a UserForm where all the data has to be entered using UserForm and recorded in one Excel Sheet. And then print out that UserForm in A4 sized paper. I have been trying by all means by as of now, I could not do so. Any kind of help would be appreciated.
I have quite a weird situation with comboboxes. I have 2 comboboxes. 1 combobox takes its items from a range (lets say F2:F20. this combobox checks the length of the data and includes it all). Based on the value selected in the other combobox the data changes in the range mentioned above.
My code seeems to work and everything seems fine. However on random occasions, when flipping back and forth between items in the combobox that changes the data that then feeds into the other combobox, i see that the data is infact incorrect and is of the previous selection. Is this because the combobox is working faster than the info is updating on the excel spreadsheet?
I can find no solution any ideas would help