combobox list filtering 1

[gremlins]

I have built a userform that has combox lists in it. The first list uses the row source to look for it's values in the first sheet. I then want to filter what shows in the second box on the basis of what is selected in the first one.

So column A in the worksheet list something along the lines of:

Bakery (by)
Chemist (ct)
Cobblers (cs)

Column B has the list:

by/cakes
by/bread
by/pasties
ct/toothbrush
ct/pills
ct/deodorant
ct/shampoo
cs/shoes
cs/shoelaces

What I need to do is filter the second list that shows in combobox2 on the basis of the selection made in combobox1.

I'm sure there must be an easy way to do this using tables but I can't find it. It is possible for me to put the two letter code in a column next to the appropriate items.

I want to move away from the idea of iterating through the list and writing the correct values to an additional column.

Any help would be gratefully appreciated.

Thanks

 

[mdav2]

I've used the example you have given and come up with a spreadsheet to do this. Here is the code I used in a form. If you want I can send you the spreadsheet to save you having to set it all up.


' Declare public variables
Dim nMainCounter As Integer
Dim nFilteredCounter As Integer


Private Sub ComboBox1_Change()

Dim nResultsCounter

If IsNull(ComboBox1.Value) = True Then
Else

Sheets("QueryResult").Select

[a2].Select
ActiveCell.Value = ComboBox1.Value

[a7].Select
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
[a7].Select

x = nFilteredCounter + 1

Sheets("Table2").Range("A1:B" + Trim(Str(x)) + "").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A7"), Unique:=False

' Count results
nResultsCounter = 0
Sheets("QueryResult").Select
[a8].Select
Do While Len(ActiveCell.Value) > 0

nResultsCounter = nResultsCounter + 1

ActiveCell.Offset(1, 0).Range("A1").Select

Loop

ReDim array2(nResultsCounter - 1)

[a8].Select
For x = 0 To nResultsCounter - 1

array2(x) = ActiveCell.Value

ActiveCell.Offset(1, 0).Range("A1").Select

Next x

ComboBox2.List = array2

End If

End Sub

Private Sub UserForm_Initialize()

nMainCounter = 0
nFilteredCounter = 0

' Count table1
Sheets("table1").Select
[a2].Select
Do While Len(ActiveCell.Value) > 0

nMainCounter = nMainCounter + 1

ActiveCell.Offset(1, 0).Range("A1").Select

Loop

' Count table2
Sheets("table2").Select
[a2].Select
Do While Len(ActiveCell.Value) > 0

nFilteredCounter = nFilteredCounter + 1

ActiveCell.Offset(1, 0).Range("A1").Select

Loop

' Create array for table 1 selection
ReDim array1(nMainCounter - 1, 1)

Sheets("table1").Select
[a2].Select
For x = 0 To nMainCounter - 1
For y = 0 To 1

array1(x, y) = ActiveCell.Value

ActiveCell.Offset(0, 1).Range("A1").Select

Next y

ActiveCell.Offset(1, -y).Range("A1").Select

y = 0
Next x

ComboBox1.List = array1

End Sub

 

[gremlins]

cool. that'd great if you could. zidbinger@hotmail.com