Eliminate pivot table message

TinaP

Well-known Member
Joined
Jan 26, 2005
Messages
528
I have created a pivot table which displays the amount for cash drawers over a user-selected period of time. Below the pivot table, there are a few rows with statistics for each drawer. The statistics are deleted and regenerated after each pivot table update (using the worksheet event). Because the user can select periods of time of varying lengths, the pivot table will frequently try to overwrite the statistics before the pivottableupdate macro has a chance to take affect and I get a message saying “Do you want to replace the contents of the destination cells in [Cash Drawers.xlsm]PIVOT?”

Is there any way to eliminate the message since the macro will deal with it anyway?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
application.enablewarnings = false maybe, I don't have a pivot to test it with
 
Upvote 0
I was hoping to avoid using application.displayalerts = false in case the user had more than one file open. I guess there's no other way around it, though.
 
Upvote 0
you might want to set it true a couple of lines later so it does its job just when you want it
 
Upvote 0
I'm not sure if I understood you correctly. Do you want the pivot table to not update automatically when statistics rows are regenerated? If that's the case then you can set the ManualUpdate property of the pivot table to True, and update/recreate the table in your pivottableupdate macro, handling the overwrite situation there.
 
Upvote 0
I want the pivot table to update automatically. However, if the data area of the pivot table is going to expand where it will overwrite the statistics, it will give the message that I listed above. I would like to eliminate that message, if possible.

The suggestion by Mole999 hasn't worked because I can't find a worksheet event where I can use application.displayalerts = false, before the message appears. Thus far I've tried Change, Calculate, PivotTableAfterValueChange, PivotTableBeforeAllocateChanges, PivotTableBeforeCommitChanges, PivotTableBeforeDiscardChanges, PivotTableChangeSync, and PivotTableUpdate. The message appears before each worksheet event triggers. If I use application.displayalerts = false in a workbook.open event, messages for other workbooks will not appear.
 
Upvote 0
turning this on its head, could you place the pivot further down the page and have the text above
 
Upvote 0
I thought about putting the stats above the pivot, but it created a disconnect between the data. The stats referred to the pivot column headings and putting the page filters in between was confusing.

I think I may have it sorted using Worksheet_Activate and Worksheet_Deactivate events, but I've done so much experimenting that it's all screwed up. Just when I think displayalerts should be false, it's true. And vice-versa. I have to go through everything line by line and sort out where I have displayalerts lines.

I'm sure it'll be debugged shortly. This is what I've settled on: I put an application.displayalerts = false line in Worksheet_Activate and an application.displayalerts = true in Worksheet_Deactivate and Workbook_BeforeClose.

Tonight I may celebrate with an adult beverage. Thanks to you both for your help and attention. I just with I could treat both of you to a beverage.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top