Unable to suppress warning message...

[Jeff Richman]

Hello -

I am using Excel 2002, and would like to suppress
(programmatically) the warning message which is produced
when a pivot table changes size and would overwrite non-
empty cells below or to the right of the table. The
message asks, "Do you want to replace the contents of the
destination cells in <<Worksheet Name>>?"

The warning can be produced by adding a new row field to
a table, expanding/collapsing a particular row field, or
any number of other operations that increase the size of
the pivot table causing it to need to overwrite a non-
empty cell.

I've tried setting Application.AlertBeforeOverwriting to
FALSE, and while this suppresses a similar message
resulting from drag/drop operations, it doesn't suppress
the pivot-table-related message. Is there any way to
suppress this message?

Alternatively, is there any Excel event available early
enough after a user does something to resize a pivot
table, so that I could programmatically clear the fields
that would be overwritten so that no warning would be
necessary?

Thanks.

- Jeff Richman

 

[Wei-Dong Xu [MSFT]]

Hi Jeff,

So far a I know, I'd suggest you can set DisplayAlers to false before you perform the operation.
Application.DisplayAlerts = False
After the operation, you can restore the state to normal with the codes below:
Application.DisplayAlerts = True

The default value is True. Set this property to False if you don't want to be disturbed by prompts and alert messages while a macro is running; any
time a message requires a response, Excel will chooses the default response.

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jeff Richman]

Wei-Dong -

Thanks for your quick reply. Unfortunately,
Application.DisplayAlerts doesn't seem to be doing the
job either. According to the documentation,
DisplayAlerts can only be used to suppress messages
generated during execution of a macro. The message that
I'm trying to suppress is generated by user action (not
programmatically), and I've been unable to find a way to
suppress it.

Other ideas?

- Jeff Richman

-----Original Message-----
Hi Jeff,

So far a I know, I'd suggest you can set DisplayAlers to

false before you perform the operation.

Application.DisplayAlerts = False
After the operation, you can restore the state to normal with the codes below:
Application.DisplayAlerts = True

The default value is True. Set this property to False if

you don't want to be disturbed by prompts and alert
messages while a macro is running; any

 

[Wei-Dong Xu [MSFT]]

Hi Jeff,

Thank you for replying and more information for your scenario!

This alert is a warning to let the customer know whether to replace the
data in the extended area for pivotTable. This will remind the customer
whether to replace the data. So far as I know on this issue, I don't think
you can canceal it.

Please feel free to let me know if you have any further questions.

Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.