How to run a VBA macro on a protected sheet

When a worksheet is protected the VBA macros will encounter an error if they attempt to make changes to the sheet. One solution is to unprotect the worksheet, run the macro, and then protect the sheet again.

Sheets("sheetname").UnProtect password:="password"...your macros....Sheets("sheetname").Protect password:="password"

The disadvantages of this approach are that the password must be displayed in the VBA code, the worksheet will be temporarily unprotected and it will remain unprotected if the macro is interrupted.A better solution is to use userinterfaceonly:=true which will allow the macro to make changes to a protected sheet.

Sheets("sheetname").UnProtect userinterfaceonly:=true..your macros...

Place this line at the beginning of your macro. It will remain in effect for the life of the macro. ', '
"Yes, Excel can do that!"
© 2000-2014 All rights reserved.