Event Before refresh pivot cache

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 ?

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

Seems to work

Code:

---

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

---

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

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)

Code:

---

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



---

Quote:

---

Originally Posted by Paul_Hossler

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

Quote:

---

Originally Posted by Artik

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

Quote:

---

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

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

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

On the example provided by Paul.

XML code:

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:

Code:

---

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

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