"Run-Time error 91; object variable not set" message received

[dsheard2015]

Hello,

I have numerous worksheets that I am trying to add "allow users to edit ranges" permissions. There are a lot of worksheets so I am hoping to accomplish this using vba and written in to the sheet code. I have come up with a code but it returns a "run-time error 91; object variable not set" message. I'm not even sure if this is the correct way of getting what I hope for so I am very open to any changes.

Any help I can get with this is greatly appreciated?

Thanks,

Dave

Sub SetAllowEditRanges()

    Dim Sht As Worksheet
    
        Sht.Protection.AllowEditRanges.Add Title:="Grade", Range:=Range("C28:L28,C61:L61,C94:L94,C127:L127,C160:L160,C193:L193,C226:L226"), Password:=Sheets("Worksheet Names").Range("O11").Value
        Sht.Protection.AllowEditRanges.Add Title:="Student Initials", Range:=Range("C29:L29,C62:L62,C95:L95,C128:L128,C161:L161,C194:L194,C227:L227"), Password:=Sheets("Worksheet Names").Range("O11").Value
        
End Sub

 

[AndyPandy27]

Hi dsheard2015,

I think your issue is that you've created a variable called "Sht", but you haven't Set that to anything, so it's currently an empty variable.

If you Set it, using something like the following, you should be ok:

Set Sht = Worksheets("Your Sheet Name Here")

Good luck!

AP

 

[dsheard2015]

Hello AP,

Thanks for your reply. I finally got the code to work somewhat close to what I was wanting. The code sets the password and will unlock the cells like its supposed to but when I change the password in the cell specified in the code it does not get updated or work to unlock cells. Basically, now, I'm trying to find a way to have the password update when it is changed.

Here is the updated code..

Thanks again for your suggestion,

Dave

Sub SetAllowEditRanges()
 
    ActiveSheet.Protection.AllowEditRanges.Add Title:="Grade", Range:=Range("C28:L28,C61:L61,C94:L94,C127:L127,C160:L160,C193:L193,C226:L226"), Password:=Sheets("Worksheet Names").Range("O27").Value
    ActiveSheet.Protection.AllowEditRanges.Add Title:="Student Initials", Range:=Range("C29:L29,C62:L62,C95:L95,C128:L128,C161:L161,C194:L194,C227:L227"), Password:=Sheets("Worksheet Names").Range("O27").Value
       
End Sub