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)
- Press with left mouse button on Developer tab on the ribbon. How to show developer tab
- Press with left mouse button on Insert button.
- Press with left mouse button on Combo box
- Create a combo box on a sheet.
(Press 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. Press with left mouse button on 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. Press with right mouse button on combobox and press with left mouse button on "Assign Macro...". See picture below.
This means when you press with left mouse button on 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 press with left mouse button on 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
Combobox category
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
In this post I am going to demonstrate two things: How to populate a combobox based on column headers from […]
Form controls category
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 blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code. The list box […]
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 […]
Excel categories
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