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


"Trevor Williams" wrote:

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

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" wrote:

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" wrote:

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 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" wrote:

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

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" wrote in
message ...
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