Populate a combo box (form control) [VBA]
In this tutorial I am going to explain how to:
- Create a combo box (form control)
- Filter unique values and populate a combo box (form control)
- Copy selected combo box value to a cell
- Refresh combo box using change events
Create a combo box (form control)
- Click Developer tab on the ribbon. How to show developer tab
- Click Insert button.
- Click Combo box
- Create a combo box on a sheet.
(Click and hold left mouse button on a sheet. Drag down and right. Release left mouse button.)
The question now is how do you know the name of a combo box? You have to know the name when you are writing the vba code. Left click a combo box and the name appears in the name box.
Filter unique values and populate a combo box (form control)
The code below extracts unique distinct values from column A, except cell A1. Then the code adds the extracted unique distinct values to the combo box.
'Name macro Sub FilterUniqueData() 'Declare variables and data types Dim Lrow As Long, test As New Collection Dim Value As Variant, temp() As Variant 'Redimension array variable temp in order to let it grow when needed ReDim temp(0) 'Ignore errors On Error Resume Next 'Find last not empty cell in column A With Worksheets("Sheet1") Lrow = .Range("A" & Rows.Count).End(xlUp).Row 'Populate array temp with values from column A temp = .Range("A2:A" & Lrow).Value End With 'Iterate through values in array variable temp For Each Value In temp 'Add value if character length is larger than 0 to test If Len(Value) > 0 Then test.Add Value, CStr(Value) 'Continue with next value Next Value 'Delete all items in Combobox Drop Down 1 in sheet1 Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat.RemoveAllItems 'Populate combobox with values from test one by one For Each Value In test Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat.AddItem Value Next Value 'Clear variable Set test = Nothing End Sub
Copy the code into a standard module.
Copy selected combo box value to a cell
You can assign a macro to a combobox. Right click combobox and click "Assign Macro...". See picture below.
This means when you click the combobox the selected macro is run.
The vba code below copies the selected value to cell C5 whenever the combobox is selected.
- Copy the code into a standard module.
- Assign this macro to the combobox .
Sub SelectedValue() With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat Worksheets("Sheet1").Range("C5") = .List(.Value) End With End Sub
Refresh combo box using change events
The next step is to make the combo box dynamic. When values are added/edited or removed from column A, the combo box is instantly refreshed. Copy code below Press Alt + F11 Double click sheet1 in project explorer Paste code into code window
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("$A:$A")) Is Nothing Then Call FilterUniqueData End If End Sub
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Change PivotTable data source using a drop-down list
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
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 […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
10 Responses to “Populate a combo box (form control) [VBA]”
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.
Your Sharing is enjoyful to me, i got some help from your posting.
Hello,
Very helpfull post. How can I add unique and ordered values to the combobox?
Thank you
Anibal
Anibal,
See attached file:
Populate-combo-box-unique-sorted-values.xlsm
[...] Could you please nudge me towards right direction? Thanks Rajesh have a look at sample code here Excel vba: Populate a combo box (form control) | Get Digital Help - Microsoft Excel resource You can amend code if using an active x control on your worksheet. [...]
Hi I tried your code, but instead of having a combo box in sheet 1, i used it in sheet2 everything is correct except if there were updates made on the selection on sheet1 the combobox in sheet2 is not updating, can you help me on this may be i miss some code?
Ver,
Try this:
[...] I wonder whether someone may be able to help me please. I'm using this example Excel vba: Populate a combo box (form control) | Get Digital Help - Microsoft Excel resource to create a combo box which contains unique values from a given spreadsheet list. If I use the [...]
If you are going for finest contents like me, just pay a quick visit this web site everyday as it provides quality contents,
thanks
Excelente codigo. Me fue de mucha utilidad!
JULIAN LOZANO,
Gracias