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.