Protect Sheet Using VBA

anwilson

New Member
Joined
Apr 17, 2019
Messages
13
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:

Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,691
I'm guessing the error is on the Call line. If so, try this after you replace the password (in red):
Rich (BB code):
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

New Member
Joined
Apr 17, 2019
Messages
13
I'm guessing the error is on the Call line. If so, try this after you replace the password (in red):
Rich (BB code):
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,691
Did you replace the old script and save the file before you closed it?
 

anwilson

New Member
Joined
Apr 17, 2019
Messages
13
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,691
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?
 

Forum statistics

Threads
1,085,255
Messages
5,382,600
Members
401,797
Latest member
meloanthony

Some videos you may like

This Week's Hot Topics

Top