Results 1 to 9 of 9

Thread: Event Before refresh pivot cache

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #8
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •