Toggle Change Procedure

[iflyfr8]

I currently use the following to automatically refresh a group of pivot tables:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ActiveWorkbook.Worksheets
For Each pt In Worksheets("QRY_Open").PivotTables
pt.PivotChache.Refresh
Next
Next
End Sub

The source data uses the RTD function and updates continuously. I would like to create a toggle button to turn the auto refresh on/off.

My VBA knowledge is very beginner. How do I make the toggle button deactivate this code which is normally always on?

Thanks

 

[Oaktree]

Is this the only VBA event?

If so, your toggle could be as simple as

Sub Toggle()
Application.EnableEvents = Not (Application.EnableEvents)
[A1] = Application.EnableEvents 'to list true/false in cell A1 of the current sheet for whether events are enabled 
End Sub

 

[John_w]

Your code shows you are refreshing pivot tables only on the QRY_Open sheet, therefore you don't need the For Each ws loop.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim pt As PivotTable
    
    If RefreshPivotTables Then
        Application.EnableEvents = False
        For Each pt In Worksheets("Report").PivotTables
            Application.StatusBar = "Refreshing " & pt.Name
            pt.PivotCache.Refresh
        Next
        Application.StatusBar = ""
        Application.EnableEvents = True
    End If
    
End Sub

In a standard module:

Public RefreshPivotTables As Boolean

Public Sub ToggleRefresh()

    Dim button As Shape
    
    If Not IsError(Application.Caller) Then
        'Procedure called by command button click
        Set button = ActiveSheet.Shapes(Application.Caller)
    Else
        'Procedure called directly - get first shape on sheet
        Set button = ActiveSheet.Shapes(1)
    End If
    
    If RefreshPivotTables Then
        RefreshPivotTables = False
        button.TextFrame.Characters.Text = "Pivot table automatic refresh is OFF - click to turn it ON"
    Else
        RefreshPivotTables = True
        button.TextFrame.Characters.Text = "Pivot table automatic refresh is ON - click to turn it OFF"
    End If
    
End Sub

Assign the above ToggleRefresh macro to the command button. The RefreshPivotTables flag is initially False, unless you explicitly set it to True elsewhere, therefore the caption of the button should indicate this, e.g. "Pivot table automatic refresh is OFF - click to turn it ON"