get-digital-help.com

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)

1. Click Developer tab on the ribbon. How to show developer tab
2. Click Insert button.
3. Click Combo box
4. 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.

1. Copy the code into a standard module.
2. 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

Related articles

---

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 […]

Create a Print button [VBA]

This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]

Populate a combobox with values from a pivot table [VBA]

In this post I am going to demonstrate two things: How to populate a combobox based on column headers from […]

Working with TEXT BOXES [Form Controls]

What's on this page Insert a text box Font and font size Add or edit text Position a text box […]

Scroll through a data set [VBA]

Sometimes you just want to show a small section of your data set, like in a dashboard or a chart. […]