• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Slicer change triggering macro - however....

C

Cantonalives

Member
Hi all,
I have a macro that unhides key tabs, refreshes a workbook and hides tabs again which works fine.

I also need a macro to fire if a slicer changes. The macro is very similar to the above, but I find I cannot do it via 'Worksheet_PivotTableUpdate' due to the fact that the above 'refresh' causes issues as it effectively runs twice. Upon the refresh macro and then also on the PT update.

I was thinking I could had a table that contains all items and counts them, so any number change would trigger it, however it one slicer items is changed for another, the number would be the same...

Any help round this gratefully received. I don't think I can upload this issue due to sensitive information, but will try think how i can dummy it.

Thanks,


-CL..
 
Thanks for the reply. I have gone with a character count using - SUM(LEN(A61:A2000)) on a table containing the slicer names. This should not change under a standard refresh but will change if the slicer is altered. Now I will need a VB solution to run a macro should that cell alter.. Hopefully the theory is sound.

Thanks,

-CL
 
Hi again,
I thought my solution could work, however I can see the cell changing based on the slicer, but nothing triggers... I have used the below code - any help much appreciated.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$a$58" Then
        MsgBox ("Cell a58 Has Changed.")
    End If
End Sub
 
The change event is not triggered by a formula calculation. You can use the Calculate event but it will be triggered whenever anything in the sheet calculates - you can't restrict it to just one cell. Alternatively you could link an activex textbox to the formula cell and use its Change event.

I would still suggest that you disable events in the macro instead.
 
Thanks again.. Disable events? Not used before - how would this look in the context of what I am looking to achieve?

Thanks - CL.
 
Found this which seems to work for this solution where a Formula updates a cell.

Code: 
Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Range("A59").Value <> OldVal Then
    OldVal = Range("a59").Value
    Call Macro
End If
End Sub

Thanks for your patience..


-CL.
 
To disable events, your macro would look something like this:
Code: 
Sub Macro()
Application.EnableEvents = False
' do things here
Application.EnableEvents = True
End Sub

You'll want some error handling to ensure that events are re-enabled even if something goes wrong.
 
Static statement should be like as to utilized at another task

Code: 
Static OldVal As Variant

Private Sub Worksheet_Calculate()
 
No, Static variables belong at procedure level. Not much point using Static like that as Dim would do the same thing.
 
Last edited:
I tweeked Cantonalives suggestion a little bit and used Dim variable as Range rather than Variant

>>> use code - tags <<<
Code: 
Private Sub Worksheet_Calculate()

Dim OldVal As Range
Dim rng As Range

Application.ScreenUpdating = False

Set rng = Range("A19")  'where value is for example 25
Set OldVal = Range("B16")  'where value is for example 55

If rng.Value <> OldVal.Value Then

    Cells.EntireColumn.AutoFit
  
Application.ScreenUpdating = True
 
End If

End Sub
 
Last edited by a moderator:
You must log in or register to reply here.
Back
Top