© Roy Cox (2005 - 2020)

The UserInterFaceOnly is an optional argument of the Protect Method that you can set to True, the default is False. By setting this argument to True Excel will allow all Excel VBA macros to run on the Worksheet that has been protected with or without a password.

"Run-time error '1004': Application-defined or object-defined error"

if this error occurs after running code that has been run successfully in the past then it is probably because you 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. The common way that is recommended is to add a line of code at the beginning of your macro that will unptotect the sheet to allow the code to work on that sheet. This works fine, but you need to remember to restore Protection when the code finishes and if an error in the code occurs you might end up with an unprotected sheet.

Error Handler

To prevent this from happening you need to add an error handler into your code. Error handling is something that you should be aware of for your VBA code. It consists of telling Excel what to do when an error occurs. On Error Resume Next: This line tells Excel that when a run-time error occurs to pass control to the statement immediately following the statement where the error occurred, and code execution continues from that point. T he On Error GoTo 0: This statement turns off error trapping. It disables an enabled error handler in the current procedure and resets it to Nothing. On Error GoTo: This enables the error-handling routine that starts at the specified Line. The On Error GoTo statement traps all errors, regardless of the exception class. TIP: Do not add the error handler until any code you are working on has been thoroughly tested, this will hide all errors and if possible you need to correct any errors before addin the error handler.

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 so that 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. There is another potential security issue. If anyone looks at your code in the VB Editor then the password cab be seen in your VBA code. To avoid this you will need to protect your VBA project from viewing by locking it in the VB Editor. This does actually apply to both methods, read this to see how to protect your code . You can also download an example workbook that demonstrates this method.
Sub MyMacro() On Error GoTo err_exit ActiveSheet.Unprotect "Secret" Range("A1").Value = "Hello" err_exit: ActiveSheet.Protect "Secret" End Sub

Code with error handler

Excel Protect with Userinterfaceonly

Page Title

Making Excel work for you.
Sub MyMacro() ActiveSheet.Unprotect "Secret" Range("A1").Value = "Hello" ActiveSheet.Protect "Secret" End Sub

Simple code to run on protected sheet

‘Place in the WorkBook Open event Private Sub Workbook_Open() Dim oWs As Worksheet ‘’///protect all worksheets with UserInterfaceOnly For Each oWs In ThisWorkbook.Worksheets oWs.Protect Password:="Secret", UserInterFaceOnly:=True Next oWs End Sub ‘Place in a Standard Module Const sPw As String = "Secret" ''///these two macros work with UserInterFaceOnly applies Sub x() Sheet1.Range("A1").Value = "Hello World" End Sub Sub y() Sheet1.Range("A1").ClearContents End Sub ''/// these don't so unprotect/re-protect is necessary Sub Add_Line_ListObject() Dim oTbl As ListObject Dim oNewLine As ListRow On Error GoTo err_exit With Sheets("Sheet4") Set oTbl = .ListObjects(1) .Unprotect sPw oTbl.ListRows.Add AlwaysInsert:=False err_exit: .Protect sPw End With End Sub Sub DeleteRow() With ActiveSheet .Unprotect sPw On Error GoTo err_exit ''/// demo only, needs to be dynamic .ListObjects(1).ListRows(2).Delete err_exit: .Protect sPw End With End Sub

Sample Code

If you find this article useful You can help maintain my site by donating. Simply click the image below
Buy me a coffeeBuy me a coffee

© Ut duis incididunt ex officia

The UserInterFaceOnly is an optional argument of the Protect Method that you can set to True, the default is False. By setting this argument to True Excel will allow all Excel VBA macros to run on the Worksheet that has been protected with or without a password.

"Run-time error '1004': Application-defined or object-

defined error"

if this error occurs after running code that has been run successfully in the past then it is probably because you 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. The common way that is recommended is to add a line of code at the beginning of your macro that will unptotect the sheet to allow the code to work on that sheet. This works fine, but you need to remember to restore Protection when the code finishes and if an error in the code occurs you might end up with an unprotected sheet.

Error Handler

To prevent this from happening you need to add an error handler into your code. Error handling is something that you should be aware of for your VBA code. It consists of telling Excel what to do when an error occurs. On Error Resume Next: This line tells Excel that when a run-time error occurs to pass control to the statement immediately following the statement where the error occurred, and code execution continues from that point. T he On Error GoTo 0: This statement turns off error trapping. It disables an enabled error handler in the current procedure and resets it to Nothing. On Error GoTo: This enables the error-handling routine that starts at the specified Line. The On Error GoTo statement traps all errors, regardless of the exception class. TIP: Do not add the error handler until any code you are working on has been thoroughly tested, this will hide all errors and if possible you need to correct any errors before addin the error handler.

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 so that 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. There is another potential security issue. If anyone looks at your code in the VB Editor then the password cab be seen in your VBA code. To avoid this you will need to protect your VBA project from viewing by locking it in the VB Editor. This does actually apply to both methods, read this to see how to protect your code . You can also download an example workbook that demonstrates this method.
Sub MyMacro() On Error GoTo err_exit ActiveSheet.Unprotect "Secret" Range("A1").Value = "Hello" err_exit: ActiveSheet.Protect "Secret" End Sub

Code with error handler

Excel Protect with Userinterfaceonly

Page Title

Logotype