Alternate to Sheet_Calc when Filter applied.

  • Thread starter Thread starter Trevor Williams
  • Start date Start date
T

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
 
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
 
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
 
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
 
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?
 
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)
 
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
 
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
 
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?
 
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
 
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
 
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
 
Back
Top