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
Highlighted

@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
Counting Days
Tim Hunter in SQL Server on
2 Replies
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies
Set Auto Refresh to Pivot Table as Source Data Changes
ratishkp in Excel on
2 Replies
Problem sorting Pivot Table according to date
MikkoS in Excel on
1 Replies