Turn off warning messages

Sometimes the application displays a messagebox asking if the user wants to continue, if he/she wants to save a file before closing, or want some other verification from the user. These messages can be turned off by using this command in a macro :

Application.DisplayAlerts = False

The warningmessages will remain turned off until you enable them again with this command:

Application.DisplayAlerts = True

If you just want to close a workbook without the user being prompted for any confirmations about saving the workbook you can simply do this:

ActiveWorkbook.Close False 
' closes the active workbook without saving any changes

ActiveWorkbook.Close True 
' closes the active workbook and saves any changes

Document last updated 2000-02-05 12:47:32 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-12-07 23:42:58 CET):

Re: Macro
Try using the macro recorder while creating and formatting your pivot table.
You can find the macro recorder here: Tools, Macro, Record New Macro...

Andrew from Bangalore wrote (2006-12-07 06:29:50 CET):

Macro
How to format or make pivot table using macro

Ole P. from Norway wrote (2004-12-19 23:18:59 CET):

Re: Another question
You can achieve this by using e.g. the event macros "Workbook_Activate" and "Workbook_Deactivate" to turn "Application.DisplayAlerts" on or off whenever a workbook is activated or deactivated. See "Events" in the menu to the left.

Reo from Australia wrote (2004-12-19 02:50:44 CET):

Another question
Is there a way to turn off the alert.

"Some trendlines cannot be calculated from negative or zero values"

I realise that the Application.DisplayAlerts = False will turn it off during a macro run however is there a way to turn it off for a workbook or perhaps excel itself?

Ole P. from Norway wrote (2004-12-18 11:32:22 CET):

Re: Web Query
The command "Application.DisplayAlerts = False" will have an effect on built in dialogs (and "properly programmed" user dialogs) in Excel only. It will not have any effect on dialogs from other applications such as Internet Explorer (where your problem dialog is coming from).

Reo from Australia wrote (2004-12-18 07:13:58 CET):

Re: Web Query
Thanks for the reply Ole
Yes I already have that setting unchecked however it does not seem to affect the warning in excel during a refresh.

I feel that Application.DisplayAlerts = False is on the right track to prevent the warning but think that another command is needed.
Any other ideas?

Thx

Ole P. from Norway wrote (2004-12-17 21:00:44 CET):

Re: Web Query
I have no solution for this that I know will work.
There is however a setting in Internet Explorer (Tools, Internet Options, Advanced) that might solve the problem.
If you scroll almost all the way down you will find a security setting named "Warn if changing between secure and not secure mode".
I can't recommend that you uncheck this option, but you might find it worth to try and see if it solves your problem...

Reo from Australia wrote (2004-12-17 05:31:13 CET):

Web Query
Hi
I am currently running a macro that initiates a web query on a https site
that contains mixed content (both secure and non secure items) the query
keeps failing because the warning message "This site contains both secure
and non secure items. Do you want to display the non secure items?" prevents
the query from proceeding.

Any ideas on a work around? Is there a way to turn the warning messages off
I have tried adjusting settings in IE however this does not appear to turn
the warning message off in excel. Could a command be written into the macro
to click yes at the point of the warning message?

Application.DisplayAlerts = False unfortunately it did not prevent the alert from stoping the web query.

Any help appreciated.

Thanks Reo

Ole P. from Norway wrote (2004-06-11 20:13:58 CET):

Re: security level of macros
You can edit the macro security settings using the menu Tools, Macro, Security...
Setting the security level to LOW will disable the warning dialog. This is NOT recommended.
You can also avoid the macro security warning dialog by digitally signing your macros, for more detailed info visit Microsoft.

graciela from buenos aires argentina wrote (2004-06-11 17:34:34 CET):

security level of macros
i want that when i open excel document, witch has macros, don't ask me if i want to activate macros. How do i do?