CODEWITHSUNDEEP
vbaexcel
sheds141
sheds141

Reputation: 23

VBA Excel: Moving data macro error on protected sheets

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?

Upvotes: 0

Views: 3140

Answers (3)

westinq
westinq

Reputation: 43

It appears you have to reset the protection with UserInterfaceOnly = True upon Workbook_Open, which resolves my issue. Excel closes the workbook with a default of UserInterfaceOnly = False (even though I run the secure commands on the Workbook_BeforeClose event.

Upvotes: 0

f_qi
f_qi

Reputation: 699

The cells that you want to populate data in or modify needs to be unlocked. So select the range of cells, then in format cell set them to be unlocked. When you password protect the sheet, make sure you check allow to edit unlocked cells.
Try this with you original code, it should work. Since it worked when you unprotected the sheet.

Upvotes: 1

sheds141
sheds141

Reputation: 23

@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.

Upvotes: 0

Related Questions