MrExcel Publishing
Your One Stop for Excel Tips & Solutions

stop halting of a macro

Posted by Robert Johnson on March 27, 2001 6:11 PM

I'm not sure if this can be done, but I want to disable halting of a macro in my workbook. I have several sheets that are protected and some of the macros have to unprotect the sheets to run then reprotect them. I dont want users to be able to press escape or ctrl-alt-break to stop the macro while the sheets are unprotected. Can anyone help? Thank.


Posted by David Hawley on March 27, 2001 7:15 PM

Hi Bob

There are two way to handle this, with the first being my preference.

Protect your sheet(s) via VBA and set the "UserInterfaceOnly" to True. This needs to be reset again when Excel re-starts (see VBE help)

Sheet1.Protect Pasword:="secret", Userinterfaceonly:=True

Place this as the first line in your macro, this will allow the code to make changes, but NOT allow the user.

Or you can use:
Application.EnableCancelKey = xlDisabled


OzGrid Business Applications