Autofilter with Comboboxes

[ckhunley]

I have two comboboxes that I want to use as filter criteria for database. The first combobox would display criteria from column A. Then once a selection is made, the choices avaliable in combobox2 would change depending on what was selected in the first box. I can't seem to get this to work, so any recommendations you have would be great. Thanks,
Chris

 

[PHV]

If in an access form you may dynamically change the RowSource property of the second combo in the AfterUpdate event procedure of the first one.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ckhunley]

I am attempting to do this in Excel. I have tried using If...Then statements to specify the Rowsource of the second combo, but this hasn't worked. I either get nothing updated to my combobox, or I get the entire list, regardless of what was selected in the first combo. I am definitely a novice, so I am just trying anything I can think of. I am sure there is a quick simple way to do it, I just don't have a clue what it is...
Chris

 

[cLFlaVA]

Maybe this is a little excessive, but I'd probably create a sub in VBA that would be called whenever ComboBox1's value is changed.

The sub would SELECT the necessary values from the DB and re-populate ComboBox2.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[ckhunley]

That is what I am attempting to do, it is just actually getting combobox2 to repopulate that I am having issues with. What do you need to specify so that it will select the values that have been isolated from the the first combo? As I said, I am a novice, I think I might be getting in over my head with this little project I am working on, but VBA is something that I would like to learn.

Chris

 

[xlbo]

Just set the value list for combo2 equal to the result of a query with your combobox1.value set as the parameter

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ckhunley]

This is what I have so far as Code. equiptype is the first combobox, which is my first filter criteria (column A), and equip is the second combo(second criteria, column B). When I use this code, it displays the correct results in the drop down menu of equip, but it also displays all of the empty spaces corresponding to the other equipment that has been blocked out. Also, when I make a new selection in the equiptype box, rather than reset the list in equip, it just adds the results of the second filter attempt. Hopefully that makes sense, and someone will be able to help me out.

Code:

Private Sub equiptype_Change()
equiptype.Value = equiptype.Text

Worksheets("Sheet3").Activate
Range("Database2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=equiptype.Value

Dim cell As Range, rng As Range

With Worksheets(3)
Set rng = .Range("B2:B" & _
.Cells(65536, 1).End(xlUp).Row) _
.SpecialCells(xlCellTypeVisible)
End With

With Me.equip
.ColumnCount = 1
For Each cell In rng
.AddItem cell
.List(.ListCount - 1, 1) = cell.Offset(0, 1)
Next
End With
End Sub

 

[PHV]

Have you tried this ?
With Me.equip
[highlight].Clear[/highlight]
.ColumnCount = 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ckhunley]

That definitely worked...thanks. I think I put that command just about everywhere else in the code other than right there. Oh well. Any thoughts on how fix the problem with the blanks? I have to scroll down halfway through the combobox in order to find the entries that it captured with the filter.

Chris