Excel vba: Populate a combo box (form control)
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
I am using excel 2007.
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.
Sub FilterUniqueData()
Dim Lrow As Long, test As New Collection
Dim Value As Variant, temp() As Variant
ReDim temp(0)
On Error Resume Next
With Worksheets("Sheet1")
Lrow = .Range("A" & Rows.Count).End(xlUp).Row
temp = .Range("A2:A" & Lrow).Value
End With
For Each Value In temp
If Len(Value) > 0 Then test.Add Value, CStr(Value)
Next Value
Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat.RemoveAllItems
For Each Value In test
Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat.AddItem Value
Next Value
Set test = Nothing
End SubCopy 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 SubRefresh 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
Download excel 2007 macro enabled workbook *.xlsm
Related posts:
Working with combo boxes (Form Control) using vba
Excel vba: Populate a combobox with values from a pivot table
Copy selected rows (checkboxes) (2/2)
Populate a list box with visible unique values from an excel table (vba)
Recommended categories
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:
Sub FilterUniqueData() Dim Lrow As Long, test As New Collection Dim Value As Variant, temp() As Variant ReDim temp(0) On Error Resume Next With Worksheets("Sheet1") Lrow = .Range("A" & Rows.Count).End(xlUp).Row temp = .Range("A2:A" & Lrow).Value End With For Each Value In temp If Len(Value) > 0 Then test.Add Value, CStr(Value) Next Value Worksheets("Sheet2").Shapes("Drop Down 1").ControlFormat.RemoveAllItems For Each Value In test Worksheets("Sheet2").Shapes("Drop Down 1").ControlFormat.AddItem Value Next Value Set test = Nothing End Sub[...] 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 [...]