Protect Sheet Using VBA

[anwilson]

Hello,

I have a sheet that automatically updates based on information entered by the user. I need certain cells to be protected, and others not to be. I've tried the following script:

sh2.Range("B2:D2").Locked = False
sh2.Range("C4:D4").Locked = False
sh2.Range("C5:D5").Locked = False
sh2.Range("B10:B29").Locked = False
sh2.Range("C6:C7").Locked = False
Call sh2.Protect(UserInterfaceOnly:=True)

But it gives me the error: "Application-defined or object-defined error"

In the spreadsheet font color changes in a protected cell if data is entered in a different cell. That's what is bringing the error up in break mode. I figured the UserInterfaceOnly syntax would take care of that. Any suggestions?

 

[JoeMo]

I'm guessing the error is on the Call line. If so, try this after you replace the password (in red):

With sh2
    .Range("B2:D2,C4:D5,B10:B29,C6:C7").Locked = False
    .Protect Password:="Your pswd between the quote marks", UserInterfaceOnly:=True
End With

 

[anwilson]

I'm guessing the error is on the Call line. If so, try this after you replace the password (in red):

With sh2
    .Range("B2:D2,C4:D5,B10:B29,C6:C7").Locked = False
    .Protect Password:="Your pswd between the quote marks", UserInterfaceOnly:=True
End With

It works when I first put the script in and use the spreadsheet; however,if I close the file, open it again, and try to work through it I still get the same error. Any idea why?

 

[JoeMo]

Did you replace the old script and save the file before you closed it?

 

[anwilson]

I saved as a different version as a back up, replaced the script and it worked fine. I closed the excel file and opened it back up, then it gave me the error.

 

[JoeMo]

Hard to diagnose w/o seeing all your code. Can you post the code and identify which line produces the error and exactly what the error message is? Also tell us if you have any event code, like workbook before close or workbook open, that might be implicated?