Sleeper: VBA Filtering

[ALaRiva]

I'm not an Excel VBA Person by any means, I can get by only when necessary.

Well this time I need to get by and I'm too damn tired to fight with this thing.

How do you filter a sheet based on a selection in a combo box?

I've got the combo box made, it's populating appropriately.

Now I just need to filter a specific column for all of the data from Row F6 and down.

Just a quick sample will help and I can get anything else I need. I just don't use Excel enough to know where to start with filtering.

- Anthony

[johnske]

Hi Anthony,

It depends on what type of combo box you've created...The simplest one to answer is - If it's one made using the "Forms" toolbar then right-click the combo-box, select Format Control, and select "Control" then in the "Input Range" put $F6:$F100 (you can put the second input range to whatever you like 100, 1000, ...,down to 65536).

If it's a "Control" toolbar you'll need code for it. (Let me know)

As you're mentioning filtering, you could also look at Tools/Filter/AutoFilter

Regards,
John

[ALaRiva]

OK, Well here is what I found after searching. I seen alot of people use AutoFilter, so I attempted to use it, but I'm definitely missing something.

If Me.MyComboBox.Value = "X" Then
'No Filter, show all data
        Range("NewData").AutoFilter 6, "*", , , False
Else
'Filter 6th column based on combo box value
        Range("NewData").AutoFilter 6, Me.MyComboBox.Value, , , False
End If

Any Ideas?

- Anthony

[johnske]

You might find this easier, select F6, now go to Tools, Filter, and select Autofilter. You'll now see a combo-box drop-down on the RHS of F6.

Select the drop-down, then scroll down and select what you're looking for. Everything else should now be filtered out.

You can also select "Custom" and type in what you're looking for in the top RH panel.

See if that does what you want

[ALaRiva]

You might find this easier, select F6, now go to Tools, Filter, and select Autofilter. You'll now see a combo-box drop-down on the RHS of F6.

Select the drop-down, then scroll down and select what you're looking for. Everything else should now be filtered out.

You can also select "Custom" and type in what you're looking for in the top RH panel.

See if that does what you want

I already know how to do it that way, I need the combo box to be in another location which is why I'm doing it the way I am trying.

Any ideas?

- Anthony

OK, I think I'm on the right track now.

I'm using .AdvancedFilter

However, the problem I'm having is telling the code which column to filter on.

Here's what I have so far:

If Me.cboX.Value = "X" Then
Range("Criteria").Value = "*"
        Range("NewData").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Criteria"), Unique:=False
Else
Range("Criteria").Value = Me.cboX.Value
        Range("NewData").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Criteria"), Unique:=False
End If

Any Ideas?

- Anthony

[johnske]

Hi Anthony,

Try this, you'll need to have your combo box named the same as the one in the code below (ComboBox1):

Option Explicit
 
Private Sub ComboBox1_DropButtonClick()
'populate the combo box
Dim MyList(200, 1), N&
For N = 6 To 200
MyList(N - 6, 0) = Range("F" & N)
Next N
ComboBox1.List = MyList
End Sub
 
Private Sub ComboBox1_Change()
With Range("F1:F200")
If ComboBox1 = "X" Then
'No Filter, show all data
.AutoFilter 1, "*", , , False
Else
'Filter 6th column based on combo box value
.AutoFilter 1, ComboBox1, , , False
End If
End With

[ALaRiva]

With that I'm getting AutoFilter Method of Range Class Failed.

Any Ideas on the AdvancedFilter Method I was trying?

- Anthony

[johnske]

With that I'm getting AutoFilter Method of Range Class Failed.

Any Ideas on the AdvancedFilter Method I was trying?

- Anthony

Don't know why you're getting an error - try the attachment, I've been using this with no probs...

As to AdvancedFilter - No - I've never used it so can't say.

Regards,
John