Home

Warning before pivot table refreshes

%3CLINGO-SUB%20id%3D%22lingo-sub-1019932%22%20slang%3D%22en-US%22%3EWarning%20before%20pivot%20table%20refreshes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1019932%22%20slang%3D%22en-US%22%3E%3CP%3EMorning%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20had%20a%20look%20in%20these%20forums%20and%20online%20but%20can't%20see%20a%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20after%20some%20sort%20of%20warning%2Fconfirmation%20notice%20in%20excel%20to%20appear%20just%20before%20a%20pivot%20table%20is%20refreshed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%20If%20not%2C%20are%20there%20any%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1019932%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1020095%22%20slang%3D%22en-US%22%3ERe%3A%20Warning%20before%20pivot%20table%20refreshes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1020095%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F462940%22%20target%3D%22_blank%22%3E%40Rustle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20use%20%3CSTRONG%3EWorksheet_PivotTableUpdate%3C%2FSTRONG%3E%20Event%20to%20achieve%20that.%3C%2FP%3E%3CP%3ETo%20do%20so%2C%20follow%20these%20steps...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3ERight%20Click%20on%20Sheet%20Tab%20Name%20which%20has%20the%20Pivot%20Table%20and%20choose%20View%20Code.%3C%2FLI%3E%3CLI%3ECopy%20the%20code%20given%20below%20and%20paste%20it%20into%20the%20opened%20code%20window.%3C%2FLI%3E%3CLI%3EClose%20the%20VB%20Editor%20and%20save%20your%20workbook%20as%20Macro-Enabled%20Workbook.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CU%3E%3CSTRONG%3ECode%3A%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_PivotTableUpdate(ByVal%20Target%20As%20PivotTable)%0ADim%20Ans%20As%20VbMsgBoxResult%0A%0AOn%20Error%20GoTo%20Skip%0AApplication.ScreenUpdating%20%3D%20False%0AApplication.EnableEvents%20%3D%20False%0A%0AAns%20%3D%20MsgBox(%22Are%20sure%20that%20you%20want%20to%20refresh%20the%20Pivot%20Table%3F%22%2C%20vbQuestion%20%2B%20vbYesNo%2C%20%22Confirm%20Please!%22)%0A%0AIf%20Ans%20%3D%20vbNo%20Then%0A%20%20%20%20Application.Undo%0A%20%20%20%20MsgBox%20%22Pivot%20Table%20is%20not%20refreshed.%22%2C%20vbExclamation%0AElse%0A%20%20%20%20MsgBox%20%22Pivot%20Table%20has%20been%20refreshed%20successfully.%22%2C%20vbInformation%0AEnd%20If%0ASkip%3A%0AApplication.EnableEvents%20%3D%20True%0AApplication.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20per%20the%20above%20event%20code%2C%20when%20you%20try%20to%20refresh%20the%20Pivot%20Table%2C%20it%20will%20prompt%20you%20to%20confirm.%3C%2FP%3E%3CP%3EIf%20you%20click%20on%20Yes%2C%20the%20Pivot%20Table%20will%20get%20refreshed%20successfully%20but%20if%20you%20click%20on%20No%2C%20the%20Pivot%20Table%20will%20not%20get%20refreshed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20this%20is%20what%20you%20were%20trying%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Rustle
Occasional Visitor

Morning all,

 

I've had a look in these forums and online but can't see a solution.

 

I'm after some sort of warning/confirmation notice in excel to appear just before a pivot table is refreshed.

 

Is this possible? If not, are there any suggestions?

1 Reply

@Rustle 

You may use Worksheet_PivotTableUpdate Event to achieve that.

To do so, follow these steps...

 

  1. Right Click on Sheet Tab Name which has the Pivot Table and choose View Code.
  2. Copy the code given below and paste it into the opened code window.
  3. Close the VB Editor and save your workbook as Macro-Enabled Workbook.

Code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim Ans As VbMsgBoxResult

On Error GoTo Skip
Application.ScreenUpdating = False
Application.EnableEvents = False

Ans = MsgBox("Are sure that you want to refresh the Pivot Table?", vbQuestion + vbYesNo, "Confirm Please!")

If Ans = vbNo Then
    Application.Undo
    MsgBox "Pivot Table is not refreshed.", vbExclamation
Else
    MsgBox "Pivot Table has been refreshed successfully.", vbInformation
End If
Skip:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

As per the above event code, when you try to refresh the Pivot Table, it will prompt you to confirm.

If you click on Yes, the Pivot Table will get refreshed successfully but if you click on No, the Pivot Table will not get refreshed.

 

Let me know if this is what you were trying to achieve.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies