Solved: Dependent combo boxes in a User form

[simora]

In my Workbook Book1WedTest.xls userform, I am trying to have ComboBox2 populated from the selection made in ComboBox1
Then, Set that as the search criteria ( Set ComboBox2 = TextBox1 )

I can't get the ListView to display all of the Row and Column data.

Also, I want to ADD another textbox & search button.
How do you search the whole database and display the data in the Listview window .

Any help appreciated.

[Bob Phillips]

Gets an object error on my Win 7 64bit system.

[snb]

To give an impression cfr. teh attachment

[simora]

snb Thanks.
However, I got an error in the Private Sub ComboBox3_Change at the following point.
sp(j, 1) = Val(sp(j, 1))

I'm running XP and office 2003

[simora]

OOPs!

The error is
Run-time error 9
Subscript out of range.

[simora]

I was able to resolve most of the issues, but one line of code in the
Private Sub ComboBox1_Change()
is causing a problem. I cant figure out how to clear all the values. The first search is OK, however, when I try to do a 2nd search on any field other than the Company Name , this line of code throws an error.


If sn(j, ComboBox1.ListIndex) <> "" And InStr("|" & c01, "|" & sn(j, ComboBox1.ListIndex) & "|") = 0 Then c01 = c01 & "|" & sn(j, ComboBox1.ListIndex)


The Error is:
Run-time error '9';
Subscript out of range

See modified worksheet attached.
Really need help on this. Thanks

[simora]

In case anyone needed a working solution, I Changed the whole Private Sub ComboBox1_Change() structure:

Dim Rng
Dim LastRow As Long
Dim Col As Variant
Col = ComboBox1.ListIndex + 1
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
' THIS SETS THE COLUMN AND LASTROW RANGE
Set Rng = Range(Cells(2, Col), Cells(LastRow, Col))
For Each cell In Rng
ComboBox2.AddItem cell.text
Next

[Bob Phillips]

You can avoid the loop

Dim Col As Variant
Col = ComboBox1.ListIndex + 1
ComboBox2.List = Application.Index(Application.Transpose(Range(Cells(2, Col), Cells(2, Col).End(xlDown))), 1, 0)

[snb]

You can avoid more:

Listbox2.list=cells(1).currentregion.value

[simora]

Thanks All!
snb: Did you ever figure out what the problem was in your original code ?


If sn(j, ComboBox1.ListIndex) <> "" And InStr("|" & c01, "|" & sn(j, ComboBox1.ListIndex) & "|") = 0 Then c

[simora]

Hi:
Problems with using your suggested code:
snb: Your code only works for column 1. It never gets to col 2 or beyond. The point is to search whichever column is selected.

XLD: Your code only lists values before there's a 0.00 or no value in a column. It does NOT capture all the values in a column.