How can run a macro ( call a macro) on selection of any filtercriteria?

[milindkeer]

Hi

How can run a macro ( call a macro) on selection of any filter
criteria?

I have autofilter set and now I wanna call macro on changing my
autofilter? how to do that?

basically Autofilter in my even and on click how to call macro??

Thanks!!

 

[CurlyDave]

Hi

How can run a macro ( call a macro) on selection of any filter
criteria?

I have autofilter set and now I wanna call macro on changing my
autofilter? how to do that?

basically Autofilter in my even and on click how to call macro??

Thanks!!

What do you want to do with the filtered data?

 

[milindkeer]

Hi

Thanks for the reply.

I want to set a filtered criteria to some other cell.

e.g. I have a filter set on Country column, now when I select UK,
obviously my filter will display records against UK but, on selection
of UK i want to call a macro which will set a filter criteria value
(i.e. UK) to a cell lets say A1.

Note: I have already wrote a function to retreive filter criteria and
want to call in on change of filter criteria, so basically looking for
a trigger to call this function.

Thanks!
Milind

 

[CurlyDave]

Try the worksheet Change event, it goes into the worksheet module,
right click on the sheet tab and select View Code, Paste the code
there

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then YourMacro
End Sub
YourMacro will actually be the name of your macro, and will run when
you change A1

 

[milindkeer]

Hi Dave

Thanks again for the prompt reply. I did try this before but its not
working for filter criteria change.

It works only if you change anything in target cell and not for filter
change.

Milind

 

[austris.bahanovskis]

I don't know if there is a trappable worksheet/application event that
would trap filter change or maybe it is possible to create an object
that would raise an event in case FilterChanges.
You can have a look at brilliant site that (among other things) deals
with Application Events:
http://www.cpearson.com/Excel/AppEvent.aspx

If not possible, then I'd try to do it 'from the other side' - instead
of trying to raise event by changing filter I'd try to figure out a
way to change the filter by code - and then running the code that you
intended in the first place isn't a problem. All you'd need to do is
to limit users changing the filter manually and force them to use the
code to apply the filter (it might be easily achievable by protecting
the ws and letting only the code to apply the filter).
Hope it's somewhat helpful.

 

[austris.bahanovskis]

Just googled this:
"you can trap a filter change because a filter change triggers the
worksheet's calculate event (Worksheet_Calculate). In this event
routine you can then test to see if the filter criteria has changed
using the filter's Criteria1 and Criteria2 properties, and the On
property that indicates whether the filter is active or not."

Haven't checked myself but makes perfect sense. So, you might want to
have a public variable storing a collection (all the filter fields
with their according parameters) and when Worksheet_Calculate raises -
check if the collection has changed. There might be simpler way of
doing this, though.

 

[CurlyDave]

Hi Dave

Thanks again for the prompt reply. I did try this before but its not
working for filter criteria change.

It works only if you change anything in target cell and not for filter
change.

Milind

I am trying to read this carefully, do you want for Filter "UK" and
then Change a value in the Filtered data to a value in A1