You are here:

Excel/VBA for combobox fill off unique table values


Hi Tom,

Wondering if you can help me/direct me on how to approach my newest problem.

I have an excel table (structured table) that has a list of car dealerships, their addresses, city, region etc.  On my userform, I want to be able to select values from comboboxes (or another way - i'm open on how to do this) to then populate a filtered list in the next combo box.  ie: comboboxcity is filtered to New York and now comboboxdealer shows only the dealership names that are in New York.  

I also considered embedding a pivot table into the userform with the intent of having slicers available for the user to filter the list down easily, but I'm a) not sure if I can use a slicer in a userform and b) excel 2007 doesn't seem to have the control to embed a pivot table and I'm not sure how to work around that.

Do you have any suggestions on the easy way to accomplish this?  And if it is using comboboxes; what is the VBA to display just the unique values and then the vba to filter the next listing based on the first selection?

This is all I have so far:

Private Sub UserForm_Initialize()
combocity.RowSource = "dealer[City]"

But this of course gives me ALL the listings in the column.  And no idea how to filter based on another selection.

Thanks for your help!!


In you example, you used combocity as the name of the combobox, but in you narrative you said Comboboxcity and comboboxdealer.  I went with Combobox... in my example.  

This worked for me.  I had a table with the first column being "Name" and the second column being "City".  The table name was "Dealer"

all the below code was in the module associated with the Userform.

Private Sub ComboBoxcity_Click()
Dim r As Range, s As String, cell As Range
If Me.ComboBoxcity.ListIndex = -1 Then Exit Sub
Set r = Worksheets("Sheet1").Range("Dealer[Name]")
s = Me.ComboBoxcity.Value
For Each cell In r
If LCase(cell.Offset(0, 1).Value) = LCase(s) Then
  Me.ComboBoxDealer.AddItem cell.Value
End If

End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()

'code adapted from John Wakenbach's
Dim AllCells As Range, cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

Set AllCells = Worksheets("Sheet1").Range("Dealer[City]")
On Error Resume Next
For Each cell In AllCells
       NoDupes.Add cell.Value, CStr(cell.Value)
'       Note: the 2nd argument (key) for the Add method must be a string
Next cell

'   Resume normal error handling
   On Error GoTo 0

'   Sort the collection (optional)
   For i = 1 To NoDupes.Count - 1
       For j = i + 1 To NoDupes.Count
         If NoDupes(i) > NoDupes(j) Then
         Swap1 = NoDupes(i)
         Swap2 = NoDupes(j)
         NoDupes.Add Swap1, before:=j
         NoDupes.Add Swap2, before:=i
         NoDupes.Remove i + 1
         NoDupes.Remove j + 1
         End If
       Next j
   Next i

'   Add the sorted, non-duplicated items to a ListBox
   For Each Item In NoDupes
       Me.ComboBoxcity.AddItem Item
   Next Item
End Sub

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.

[an error occurred while processing this directive]