Autofilter VBA without button

[happyhungarian]

Hi,

Is it possible for an autofilter macro be setup in a way that when I update a cell that contains my criteria, the autofilter automatically runs without having to click a button to which it has been assigned? Basically, I have a section containing 7 cells that each have a seperate criteria. If the user of my workbook wants to narrow down their filter results, they will have to click the refresh button each time they change the criteria. I want the results to automatically start updating as the user adds more criteria.

Thank you,

Jesse

 

[dk]

Hi

You can probably use one of the Worksheet events. Right click on the worksheet tab and choose View Code. This will open the VB Editor. There are two dropdowns (one should say General and the other Declarations). Choose Worksheet from the first one and then Change from the second. This should create an empty procedure for you that looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

You can now write code that runs any time someone changes a cell in that worksheet. The Target argument tells you which cell(s) were changed so you can use this to determine if the changed cells were one of the 7 criteria cells that you set up. An easy way to do this is to name the criteria cells (say "Criteria") and then use something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Me.Range("Criteria"), Target) Is Nothing Then Exit Sub

'Run your autofilter code here

End Sub

HTH
DK

 

[happyhungarian]

That worked absolutely perfectly! Thanks for your help!

Jesse