PDA

View Full Version : Solved: autofilter event ... ?



Charlize
03-01-2007, 01:11 AM
Is there a way to know when someone has manually applied a filter to a choosen column. I need it because I use some coding for coloring rows. Everything goes fine when no filter is applied. When a filter is apllied all the colors are removed and code is rerun to color the rows with the knowledge that the hidden rows don't count for the coloring. But I need a way to do this automatic (or should I use a form to apply a filter ?)

Hope I was clear enough.

Charlize

Bob Phillips
03-01-2007, 01:59 AM
Sub TestAutoFilter()
Dim sh As Worksheet
Dim rng As Range

Set sh = ActiveSheet

If sh.FilterMode = False Then
MsgBox "No Active Filter"
Exit Sub
End If
Set rng = sh.AutoFilter.Range

If Intersect(Selection.EntireColumn, rng) Is Nothing Then
MsgBox "Autofilter not applied to this selection"
End If
End Sub

Charlize
03-01-2007, 04:29 AM
After some thinking I realised that I would need to force a worksheet calculate event to let my rows to be colored the way I wanted it. Now, when manually applying a filter I needed a formula to reflect the no of rows that were filtered. So I came up with this SUBTOTAAL(103;A2:A65000) & " / " & AANTAL.ALS(A2:A65000;"*")Now, this formula is recalculated everytime someone changes the filter manually. That way I can let the code for the coloring of the lines to be run.

Charlize

ps. thanks for the idea.