Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate to Sheet_Calc when Filter applied.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate to Sheet_Calc when Filter applied.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate to Sheet_Calc when Filter applied.
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate to Sheet_Calc when Filter applied.
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate to Sheet_Calc when Filter applied.
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate to Sheet_Calc when Filter applied.
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate to Sheet_Calc when Filter applied.
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate to Sheet_Calc when Filter applied.
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate to Sheet_Calc when Filter applied.
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate to Sheet_Calc when Filter applied.
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate to Sheet_Calc when Filter applied.
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 "Joel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy only visible cells after filter is applied/ sum after filter | Excel Worksheet Functions | |||
Re-calculate sum after filter is applied. | Excel Programming | |||
Refresh with Advance Filter Applied | Excel Discussion (Misc queries) | |||
FILTER applied to many coloumns | Excel Discussion (Misc queries) | |||
how do i identify first and last row once filter applied | Excel Programming |