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 article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
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 […]
There are two different kinds of text boxes, Form controls and ActiveX Controls. Form controls can only be used on […]
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
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 […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
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 a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
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