Filter Sheet From Drop Down combo box.

[adamsm]

Hi Anyone,

I've assigned a drop down combo box where I'm trying to filter the sheet when a text is selected from the drop down box.

The validation list that I'm using is =StaffList!$I$1:$I$12 where its name is MyList.

Following is the current code that I'm using.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Range("E2"), Target) Is Nothing Then
      Application.EnableEvents = False
        Range("C10").CurrentRegion.AdvancedFilter _
          Action:=xlFilterInPlace, CriteriaRange:= _
          Range("N1:N2"), Unique:=False
      Application.EnableEvents = True
  End If
End Sub

The above code works fine when I use a data validation list in cell E2.

What I'm trying to do is to filter the sheet from the Drop Down Combo box when a text from the list is selected.

The cell link that I'm using is E2.

I hope I have made my question clear.

Any help on this would be kindly appreciated.

 

[AlphaFrog]

There are two types of combo-boxes besides the data validation drop-down list. How you would initiate your filter code would depend on the type of combobox you are using.

Form Control Combo Box Type:

• Put your macro MyFilter in a standard module
• Right-click on the Combobox and select Assign Macro
• Assign the macro MyFilter (below)

Sub MyFilter()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        Range("C10").CurrentRegion.AdvancedFilter _
                     Action:=xlFilterInPlace, CriteriaRange:= _
                     Range("N1:N2"), Unique:=False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

ActiveX Combo Box Type:
In the sheet module, replace your existing Private Sub Worksheet_Change(ByVal Target As Range) code with this...

Private Sub ComboBox1_Change()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        Range("C10").CurrentRegion.AdvancedFilter _
                     Action:=xlFilterInPlace, CriteriaRange:= _
                     Range("N1:N2"), Unique:=False
    Application.EnableEvents = True
    Application.ScreenUpdating = False
End Sub

 

[adamsm]

Thank you for your valuable information and help, AlphaFrog. The code now works the way as I had requested.

Once again thanks for the help & I do really appreciate your help.