Alternate to Sheet_Calc when Filter applied.

[Trevor Williams]

Hi All

I'm using the Worksheet_Calculate event to show/hide 2 images on the active
sheet when a filter is applied. The issue is that the event fires whether
I'm on the sheet or not.

My question then is: Is there an alternate way I can show/hide the images
without using the Worksheet_Calculate event when a filter is applied?

Code below

Thanks in advance
Trevor Willams

'Worksheet module
Private Sub Worksheet_Calculate()
Call ShowClearFilterButton
End Sub

'Code Module
Sub ShowClearFilterButton()
With ActiveSheet
On Error Resume Next
If .AutoFilterMode = True Then
If .FilterMode = True Then
.Shapes("picFilter").Visible = msoTrue
.Shapes("btnFilter").Visible = msoTrue
Else
.Shapes("picFilter").Visible = msoFalse
.Shapes("btnFilter").Visible = msoFalse
End If
End If
End With
End Sub

 

[Joel]

You can use a worksheet change function. I assume the filter you are
refereing to is autofilter. Make the target cell in the worksheet change
the location if the autofilter box

Private Sub Worksheet_Change(ByVal Target as Range)
if not application.intersect(target,Range("C1")) is nothing then
'enter you code here
end if
End Sub

 

[Trevor Williams]

Hi Joel -- Thanks for the quick response.
I'm not quite sure what my Target range should be from your message.

I am using AutoFilter and the header range where the filter drop downs are
located is R17:Y17. If I set that as the Target range the code doesn't
execute.

What range should I be putting in? (maybe the range below the the headers?
e.g. R18:Y100)


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("R17:Y17")) Is Nothing Then
MsgBox ("Event Activated")
End If
End Sub


Thanks again

Trevor

 

[Joel]

You may want a different result depending on which filter is changed. It
should be the rows where the autofilter dorop down box is located.

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.address

Case "$R$17"

Case "$S$17"

Case "$T$17"

Case "$U$17"

Case "$V$17"

Case "$W$17"

Case "$X$17"

Case "$Y$17"
end select
End Sub

 

[Trevor Williams]

the Worksheet_Change event does not fire at all when a filter is changed.
I have added a break point into the code to cycle through it but it doesn't
even register the change... (?)

If I physically change the value in one of the Target cells then the event
fires. As the filter doesn't actually change the value in the target range
is there something else I should be doing?

I'm using 2002 - could that be the issue?

 

[Joel]

the worksheet chane can't be in a module sheet in VBA. It must be in the VBA
sheet for the sheet where the filters are located.

I had the wrong event. Try selection change instead.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

[Trevor Williams]

Hi Joel -- my code is in the sheet module, but still no luck with the event
firing when a filter is applied. But, again, if I select a cell in the range
then the event fires.

Any more suggestions welcome.

Trevor

 

[Joel]

Did you change form sheets change to selection change?

If you disabled events in another macro you may need to re-enable events

Run macro below and try again


sub test
Application.EnableEvents = True
end sub

 

[Trevor Williams]

Yes, I've updated the code to SelectionChange, and events are enabled.
I've even set up a new workbook as a test incase my original workbook was
the issue -- but still no joy?!

I presume it's working for you OK?

 

[Joel]

I'm using 2003 and used the code below with a break point set on the select
line. Then changed an autofilter in row 1 to a different value. It won't
trigger if you select the existing number.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target.Address

Case "$R$17"

Case "$S$17"

Case "$T$17"

Case "$U$17"

Case "$V$17"

Case "$W$17"

Case "$X$17"

Case "$Y$17"
End Select
End Sub

 

[Peter T]

Changing a filter does not directly trigger any events, though the calculate
might fire. One way to force it is with something like this -

=Subtotals(3, ref).
The value will change when any values in the range are un/hidden, and in
turn the Calculate event. Of course the Calculate event may fire
irrespective of any change to the filter. So start with say

Dim b As Boolean
b = ActiveSheet Is Me ' we're on this sheet
If b Then b = Me.FilterMode ' filter exists

If b Then
' other checks etc, eg compare old filter properties with new
' or maybe check specialcells visiblecells for hidden rows

Regards,
Peter T

 

[Trevor Williams]

I think it must be the fact I'm using 2002 as I'm still having no luck. I
suspect it's something MS built into 2003 when they applied the List
functionality.

Unless you can think of anything else, other than an upgrade, then I'll
work on a different option.

Thanks for your help Joel.

Trevor