MAKING EXCEL WORK FOR YOU
© Roy Cox 2005 to 2013
Allow macros to work on Protected Excel Sheets
"Run-time error '1004': Application-defined or object-defined error"
You have probably been searching for why your Excel VBA code has suddenly thrown up this error. It may be code that you have been running for some time with no problems. So why has Excel decided to present you with this problem? Look at what you have recently changed in the Excel Workbook. Have you decided that it would be a good idea to protect your worksheet? This should not be a problem, but If you run macros on a protected worksheet which attempt to make changes in the worksheet, you will encounter this error. So now you know what is causing the error message, but what can you do to prevent this and still keep your worksheet protected.
Change the Protection Status
The usual suggestion, and it works well, is to remove the protection, run the code then add the protection back to the sheet. You can do this within your code by adding a few lines of code. However, this method has a few problems:
Error handling is something that you should be aware of for your VBA code.
Protect Your VBA Code
To prevent unauthorised access to your VBA code you can add a password to the VBA Project to only allow access to it if the user has the password. Remember this is not 100% secure, but will prevent most users from viewing or altering your code. First,open the VBE. In the Project Explorer window (usually the left vertical pane in the VBE), select the VBA Project you want to protect. You will see your workbook in bold type like this VBAProject (YourFileName.xls). Click on Tools → VBAProject Properties →select the Protection tab → select "Lock project for viewing", then enter & confirm your password in the fields provided, and click OK. You must close your workbook and open it for this to take effect. When you next open the workbook your modules will be password protected.
The smart way
A smarter way to make sure that your run macros when a worksheet is protected would be to use the UserInterfaceOnly argument in the Protect method by setting the UserInterfaceOnly argument to True.
The UserInterFaceOnly argument is an optional argument in the Protect method and by default it is False. When you set the UserInterfaceOnly argument to True it means that the worksheet protection applies only to the user interface and does not apply to your macros. Applying this argument will allow Excel to run all macros in the worksheet. If this argument is omitted, protection applies both to macros and to the user interface.
You should be aware that if you apply the Protect with the UserInterfaceOnly argument set to True to a worksheet and then save and close the workbook, the entire worksheet will be fully protected when you reopen the workbook, but the interface protection is no longer applied.This means that to re-enable the user interface protection you must again apply the Protect method with UserInterfaceOnly set to True each time you open the workbook.
To re-enable the user interface protection after the workbook is opened, you can use the UserInterfaceOnly argument in the Workbook_Open Event wherein it gets enabled in all or the specified worksheets, each time the workbook is opened. You can also use the UserInterfaceOnly argument in a worksheet at the beginning of the macro to enable the user interface protection each time the macro is run.
Some example code for the UserInterfaceOnly method:
You can also download an example workbook that demonstrates this method.
|The Personal WorkBook|
|Excel Sheets in VBA|
|Excel UserInterfaceOnly <New>|
|Excel Macro Recorder|
|Excel Camera Tool|
|Excel Summarise Data|
|Excel CHOOSE Function|