Filter Sheet From Drop Down combo box.

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
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.
Code:
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,416
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)
Code:
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...
Code:
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

Active Member
Joined
Apr 20, 2010
Messages
444
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,273
Messages
5,657,777
Members
418,413
Latest member
Radoslaw Poprawski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top