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?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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?
 
Upvote 0
Did you replace the old script and save the file before you closed it?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top