VBA Excel: Moving data macro error on protected sheets

Question

I have the following example code in a module of VBA:

Sub My_Code()

ThisWorkbook.Sheets("Main").Range("A1") = "Main Data"
ThisWorkbook.Sheets("Secondary").Range("A2").Copy Sheets("Main").Range("B2")

End Sub

and to protect the sheets, Main and Secondary, I have put the following code in Thisworkbook of VBA:

Private Sub Workbook_Open()

Sheets("Main").Protect Password:="Mypassword", UserInterfaceOnly:=True
Sheets("Secondary").Protect Password:="Mypassword", UserInterfaceOnly:=True

End Sub

When I run My_Code() I get the error:

""Run-time error '1004' The cell or chart you're trying to change is on a protected sheet. To make changes, click Unprotect Sheet in the Review tab (you might need a password).""

And this debugs to the ThisWorkbook.Sheets("Secondary").... line.

When I manually Unprotect the Main sheet the code runs. Any ideas why I can't leave Main protected? Have I forgotten something?

Perhaps this gets you going: Thisworkbook.Sheets("Main").Range("B2").Value = ThisWorkbook.Sheets("Secondary").Range("A2").Value — jkpieterse
@jkpieterse This does solve the problem but only for this code, If I substitute this fix into my main code I get a "Application-defined or object-defined error" on the code I use to conditionally format cells . My actual program is a few 1000 lines so I can't but it in. By do you know why Protecting the code stops it from running correctly? — sheds141
One thing to watch out for here.. if you set the password in the way you have, there will be no error if it failed. So if the password isn't currently set to "MyPassword", the whole line is ignored and you're not made aware. Coupled with the fact that when the Workbook is saved, closed and reopened it removes the UserInterfaceOnly part of the protection it is quite possible that UserInterfaceOnly isn't set to the True that you think it is. Check that there are no other alternative passwords or protections being set elsewhere in your code and that the user isn't over-riding it. — CLR
@CLR Thanks for the heads up. I walked through my code and there was no other passwords or protections in my code. The password part of my code was placed in the work book under Private sub Workbook_Open() which is run when the work book is opened, so the UserInterfaceOnly is set true every time the workbook is opened. — sheds141
I've also seen people use Workbook.Activate event instead of Workbook.Open. I've never looked into why other than they have similar issues to you but this might be something to try? — CLR

sheds141 sheds141 · Accepted Answer · 2017-03-21T13:45:35

@jkpieterse Gave the solution to this question which was to change the second line of the My_Code() to

Thisworkbook.Sheets("Main").Range("B2").Value = ThisWorkbook.Sheets("Secondary").Range("A2").Value

However this created a new error in my code which is mentioned in the comments about. The who reason behind this problem is that the UserInterfaceOnly = true does not allow macros to modify the sheet, it only allows for value changes. Therefore there is no way to use the interface protect work around when you modify the worksheet. The only solution from here is:

Sub My_Code()
Dim ws as Worksheet
Set ws = ThisWorkbook.Sheets("Main")

ws.UnProtect Password:="Mypassword"

On Error GoTo ErrHandeler

ws.Range("A1") = "Main Data"
ThisWorkbook.Sheets("Secondary").Range("A2").Copy ws.Range("B2")

ws.Protect:="Mypassword"

ErrHandler:
ws.Protect:="Mypassword"
End Sub

This is the next most secure solution.

VBA Excel: Moving data macro error on protected sheets

3 Answers