Event Before refresh pivot cache

[pulsar777]

Hi, when a user right-clicks on PivotTable and selects "Refresh", I'd need to have a code immediately run after that, but before actual data "refresh". Is there any such pivot table or pivot cache event ?

[Paul_Hossler]

In the PT Update event, I (for demo) updated the PT source data, and then Refreshed the PT again

Seems to work

Option Explicit

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Worksheets("Sheet1").Range("C2").Value = 3 * Worksheets("Sheet1").Range("C2").Value
    
    Application.EnableEvents = False
    Target.RefreshTable
    Application.EnableEvents = True

End Sub

[pulsar777]

Thanks Paul. It partially works. However, event fires at any change to pivot table (adding fields, changing layout ...) not just "refresh".

[Paul_Hossler]

The only other ways I know would be to 1) have your own Refresh button (below), or to 2) automatically Refresh the PT after you make a change (manually or with macro) to the PT's source data range (attachment)

Option Explicit

Sub MyRefresh()
    Dim pt As PivotTable
    
    With ActiveSheet
        .Range("C2").Value = 3 * Worksheets("Sheet1").Range("C2").Value
    
        For Each pt In .PivotTables
            Application.EnableEvents = False
            pt.RefreshTable
            Application.EnableEvents = True
        Next
    End With
End Sub

[Artik]

The only other ways I know would be to 1) (...) 2 (...)

Paul, I recently asked about the ability to monitor the built-in ribbon buttons. Here: http://www.vbaexpress.com/forum/showthread.php?65211

What if you went this way? Do you have something against?
Such a solution would require some work, but it would be natural for the user.
But, perhaps, the button on the sheet is enough.

Artik

[Paul_Hossler]

Paul, I recently asked about the ability to monitor the built-in ribbon buttons. Here: http://www.vbaexpress.com/forum/showthread.php?65211

What if you went this way? Do you have something against?
Such a solution would require some work, but it would be natural for the user.
But, perhaps, the button on the sheet is enough.

Artik

(2) - That button was only to simulate some processing prior to refreshing the PT with some new data

I'd need to have a code immediately run after that, but before actual data "refresh".

Since I assume there will be a macro or something that updates the source data, I was just faking it with the button

The user really doesn't need to do anything since if the source data range changes, then the PT is refreshed

(1) - is just a manually initiated PT refresh, again faking some data changes and then the PT refresh


Personally I'd use (2) since I don't trust users to do the right (expected) thing and this way it should be transparent to them

[pulsar777]

Paul, thank you. That's actually a good approach when pivot table is sourced locally. In my case, pivot cache has a connection string to external source, which Excel automatically changes (removes password) when workbook is closed /reopened. In 64-bit version its a problem, only 32-bit version provides ODBC prompt window for login details (I assume it's a COM addin feature). So when a user opens a file and right clicks on pivot, selects "Refresh", immediately a code has to change connection string (add password from userform) before the connection to source is established followed by the Refresh. Otherwise Excel 64-bit will close.

Artik, thanks for interesting tip

[Artik]

pulsar777, I've read your question about an example before editing a post.

On the example provided by Paul.

XML code:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <commands >
        <command
             idMso="Refresh"
            onAction="Refresh_onAction"/>
    </commands >
</customUI >

VBA code in a standard module:

Option Explicit

'Assumptions:
'- The solution is supposed to work only for PT in sheet Sheet1 and only for the first Pivot Table.
'- For each other Pivot Table, the PT will be refreshed as a "standard".
'NOTE:
'If you select the [Refresh All] button, PT will refresh in the standard way!
'If PT is to be refreshed in a non-standard way after pressing the [Refresh All] button,
'the code (XML and VBA) should be extended by monitoring this button (idMso = "RefreshAll").


Sub Refresh_onAction(control As IRibbonControl, ByRef cancelDefault)
    Dim Wks As Worksheet
    Dim rngPT As Range
    
    Set Wks = control.Context.ActiveCell.Parent
    Set rngPT = Wks.PivotTables(1).TableRange1
    
    cancelDefault = False
    
    If Wks.Name = "Sheet1" Then
          If Not Intersect(ActiveCell, rngPT) Is Nothing Then
            cancelDefault = True
            
            MsgBox "Do something amazing here :)", vbInformation
            
            Wks.PivotTables(1).RefreshTable
          End If
    End If


End Sub

Artik

[pulsar777]

Artik, that's amazing ! Works like a clock. Really appreciated.