How to suppress "overwrite existing data" alert within a pivottable update event?

Question

Situation:

I am using a PivotTableUpdate event to clear a range to the right of a PivotTable and then insert values, in the next available column, to the right of the PivotTable.DataBodyRange.

Single slicer item selection:

In the first image below, I am selecting one slicer item and then using

.Offset(, .Columns.Count).Resize(.Rows.Count, 25)

to clear a region to the right of the DataBodyRange. The Resize is to account for prior selections which may have populated a different column from the currently next available.

I am then using

.Offset(, .Columns.Count).Resize(.Rows.Count, 1) = "1"

to set the next available free column's value to, in this case, 1.

Example: One slicer item selection, table and pivot in Sheet1:

So I am going from "before" to "after" as shown below:

This works fine. There is no change in the next available column address.

Multi-item slicer selection:

When I attempt the same thing with a multi-select the code halts with the standard "Alert before overwriting cells" i.e. I get:

There is already data in..Do you want to replace it?

Example: Multi slicer item selection, table and pivot in Sheet1:

What I have tried:

Application.DisplayAlerts = False

And

Application.AlertBeforeOverwriting = False

And (to clear the pop-up)

Application.SendKeys "{ENTER}"

The message still appears for multi-select and doesn't go away until I manually press the OK

Thoughts:

I had thought perhaps the message is triggered before the event code is run, but I don't get this message when I overwrite the same range (i.e. single slicer item selection)

I know you can manually switch this alert off via the File > Options > Advanced Settings but am unsure how to suppress, via code, in my current code.

Anyone have a solution on how to suppress this alert?

Code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Application.DisplayAlerts = False

    With Target.DataBodyRange

        Dim clearRange As Range

        Set clearRange = .Offset(, .Columns.Count).Resize(.Rows.Count, 25)

        clearRange.ClearContents

        .Offset(, .Columns.Count).Resize(.Rows.Count, 1) = "1"

    End With

    Application.DisplayAlerts = True

End Sub

Test data:

| Trust | STP  | Val |
|-------|------|-----|
| A     | STPa | 1   |
| B     | STPb | 2   |
| C     | STPc | 3   |

References:

It seems that you will need some workaround/change of approach. When PivotTableUpdate event is executed it is already too late. You may try other events https://powerspreadsheets.com/excel-vba-events/ however it probably would not help (see comments here https://stackoverflow.com/questions/21834775/how-do-i-suppress-excel-message-for-any-pivot-table-update). My tests indicate that none of the events happen before update by slicer. Btw. very nice question and post :) — TomJohn, Feb 05 '18 at 17:59
@tomjohnriddle thanks. I will have a look. The other thought I had earlier is that I simply place the insert data much further away so no risk of overlap but will require me to group some columns to keep it all in screen view. — QHarr, Feb 05 '18 at 18:02
You may programatically hide some columns and make them visible if needed. Dirty solution but will work :) — TomJohn, Feb 05 '18 at 18:03
Well my suspicion was the same as your comment about it already being too late inside the event so avoiding the possibility altogether seems to be the likely workaround. Not worried about coding the column hide think simple grouping will suffice. I will, in the real scenario, eventually put the slicer front end and backend the pivots. — QHarr, Feb 05 '18 at 18:06
So current hack is to try a public variable holding the pivottable.ColumnRange.Columns.Count where the pivotttable is a hidden one that has all the possible selection items in the columns. Then doing a plus 1 on this to set the start area for adding data i.e. no overlap can occur. This will be set in an Init procedure so is available before the event is triggered. — QHarr, Feb 05 '18 at 19:02

score 1 · Accepted Answer · answered Feb 26 '18 at 12:52

As noted, by the time the event has fired it is too late.

My hack around was to create a public variable holding the pvt.ColumnRange.Columns.Count where the pvt is a hidden duplicate pivottable (of the one in question) that has all the possible selection items in the columns. Then doing a plus 1 on this to set the start area for adding data i.e. no overlap can occur. This was set in an Init procedure so is available before the event is triggered.

How to suppress "overwrite existing data" alert within a pivottable update event?

1 Answers1