ComboBox Multiple Search

[MGumbrell]

How would I go about using multiple ComboBox's to search ie.
ComboBox 1
Complete list

ComboBox 2
A reduced list based on ComboBox1 choice

ComboBox3
A further reduced list based on ComboBox2 choice

Regards, Matt

 

[ptm0412]

Try this:

Sub Combobox1_AfterUpdate
Combobox2.Filter = "[thisfield] = Forms!Thisform.Combobox1"
end sub

And

Sub Combobox2_Afterupdate
Combobox3.Filter = "[thatfield] = Forms!Thisform.Combobox2"
End Sub

 

[MGumbrell]

Thank you

I will give that a go.

Regards, Matt

 

[MGumbrell]

I am getting an error on Filter.

Any ideas.

Thanks, Matt

 

[MGumbrell]

I have attached a sample explaining better what I am looking for.

I would be grateful for any assistance.

Regards, Matt

 

[Brianwarnock]

It appears that you want cascading combo boxes, a subject much covered in the ACCESS forum , but a technique I feel may not be achieveable in EXCEL.

Brian

 

[Brianwarnock]

Had a think as i don't like just saying no can do.

If your requirement is to place the 3rd col into a cell based on the 1st 2 then I would
a) Sort on first and second
b) Have my combobox show all 3 cols
c) A command button on the user form would place the 3rd col into requierd cell.

I have attached a sample

I have added
a new commandbutton
a new userform
named the data area array1

Brian

 

[chergh]

Have a look at this, I think it will do what you want.

 

[Brianwarnock]

Yes chergh I am sure that is what he wants.
Never did much with combos wondered how you could dynamically build a list in Excel, now I know.

Brian

 

[MGumbrell]

Absolutley perfect.

Thanks to those who contributed.

Regards, Matt

 

[mateo_feo]

Chergh
This is a start of what I was looking for, but i have modified the file (attached). What if i wanted to populate the result of the filtered line from the fourth column into another spreadsheet, and perform a calculation on that result?

to clarify, if user chooses 1st, 2nd, and 3rd mix, there will be a single result on the 4th column. How to get that result populated on a worksheet(so that a calculation can be performed)?

 

[chergh]

You would create the combobox3 change event.

So after you select a value in the third combo box you would iterate through your range until you have identified the correct row and then you can take the value from the fourth column and pass it to the other worksheet.

Something like this:

Private Sub ComboBox3_Change()


For i = 3 To ThisWorkbook.Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    
    Debug.Print ThisWorkbook.Worksheets("Sheet1").Range("B" & i).Value
    Debug.Print ComboBox2.Value
    
    If CStr(ThisWorkbook.Worksheets("Sheet1").Range("C" & i).Value) = CStr(ComboBox3.Value) And _
       CStr(ThisWorkbook.Worksheets("Sheet1").Range("B" & i).Value) = CStr(ComboBox2.Value) And _
       CStr(ThisWorkbook.Worksheets("Sheet1").Range("A" & i).Value) = CStr(ComboBox1.Value) Then
        
        ThisWorkbook.Worksheets("Sheet2").Range("A1").Value = ThisWorkbook.Worksheets("Sheet1").Range("D" & i).Value
    
    End If

Next i



End Sub

 

[mateo_feo]

Beautiful.. that works

 

[MGumbrell]

I have got a little lost. Could someone explain for the hard of thinking (like me) what the additional code has done. I have run all the new Zip files and changed the code for ComboBox3-change but unclear as to the results that I am looking for.

Thanks, Matt

 

[chergh]

Your not actually looking for anything, mateo just hijacked this thread as he was trying to do something similar to you. Start of it was at http://www.access-programmers.co.uk/forums/showthread.php?t=155476