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
> > > > >
> > > > >
> > > > >

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

> 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
> > > > > >
> > > > > >
> > > > > >

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

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

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

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

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

"Trevor Williams" wrote:

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