Alternate to Sheet_Calc when Filter applied.
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
|