Auto filter depends in a combox value

Closed
marvs - Sep 22, 2011 at 02:58 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 22, 2011 at 08:26 AM
Hi,

I'm wondering if anyone knows some formula to filter data depend in combo box value.

My spreadsheet has a column with the order status which can appear multiple times in no particular order.
I create combobox which apprears 5 status of our order at top part of my spreadsheet. Then my table spreadsheet contains multple rows and columns. On the first column where you can find the order status in particular order.
I'd want to this spreadsheet, once i select a order status in my combobox value then the below table details automatically filter those data according to the order status o choose in droplist.

Below is my formula but not working.

Private Sub ComboBox2_Change()


ActiveSheet.Shapes("ComboBox2").Select
Selection.ListFillRange = "a1:a6"

If Me.ComboBox2.Value = "open" Then
ActiveSheet.Range("$A$1:$B$12").AutoFilter Field:=1, Criteria1:=ComboBox2.Value
Range("A2").Select


ElseIf Me.ComboBox2.Value = "closed" Then
ActiveSheet.Range("$A$1:$B$12").AutoFilter Field:=1, Criteria1:=ComboBox2.Value
Range("A2").Select



ElseIf Me.ComboBox2.Value = "custom" Then
ActiveSheet.Range("$A$1:$B$12").AutoFilter Field:=1, Criteria1:=ComboBox2.Value
Range("A2").Select


ElseIf Me.ComboBox2.Value = "received" Then
ActiveSheet.Range("$A$1:$B$12").AutoFilter Field:=1, Criteria1:=ComboBox2.Value
Range("A2").Select
End If

End Sub
Is this possible? Or, I should say, is there a simple solution?
Thanks!



Related:

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Sep 22, 2011 at 08:26 AM
Hi Marvs,

I can't think of a reason why to use a combobox.
Just select the row with the headers and apply autofilter. This will create dropdownlists, select i.e. "open" and only the values with "open" will be displayed.

No need for codes, or am I overlooking something?

Best regards,
Trowa
0