Locking cell range on all worksheets, partially working.

[OfficeUser]

I am using this bit of code to perform a few things:

Sub Initialize()
Dim WS As Worksheet, shp As Shape
    For Each WS In Worksheets
        For Each shp In WS.Shapes
            If shp.Name = "lblGreen" Then shp.Visible = False
            If shp.Name = "lblRed" Then shp.Visible = True
    WS.Unprotect "password"
    WS.Range("AW10:BA10").Value = "80"
    WS.Range("S10:W10").Value = "80"
    WS.Range("AW10:BA10").Locked = True
    WS.Range("S10:W10").Locked = True
        Next shp
    WS.Protect "password"
    Next WS
End Sub

I receive an error: 'Unable to set the locked property of the range class' at this line:

WS.Range("S10:W10").Locked = True

If I remove the affected line then my code runs fine from beginning to end. Any suggestions on what may be the issue? Thanks.

 

[doofusboy]

If you are protecting your worksheets during Worksheet_Change, they are most likely getting protected again during these 2 lines:

    WS.Range("AW10:BA10").Value = "80"
    WS.Range("S10:W10").Value = "80"

If so, you could do one of 2 things, unprotect again:

    WS.Unprotect "password"
    WS.Range("AW10:BA10").Value = "80"
    WS.Range("S10:W10").Value = "80"
[COLOR=red][B]     WS.Unprotect "password"[/B][/COLOR]
    WS.Range("AW10:BA10").Locked = True
    WS.Range("S10:W10").Locked = True

Or, you could disable events so that Worksheet_Change code doesn't execute until after this routine is done:

    [B][COLOR=red]Application.EnableEvents = False[/COLOR][/B]
    WS.Range("AW10:BA10").Value = "80"
    WS.Range("S10:W10").Value = "80"
    WS.Range("AW10:BA10").Locked = True
    WS.Range("S10:W10").Locked = True
    [B][COLOR=red]Application.EnableEvents = True[/COLOR][/B]

 

[gramit]

Should the changes to the ranges not be outside of the Shape loop? You would be re-applying the sheet protection multiple times.

Perhaps the password on the spreadsheet is different to "password" and so it has not been unlocked and you cannot change the cell properties?

Sub Initialize()
Dim WS As Worksheet, shp As Shape
For Each WS In Worksheets
    For Each shp In WS.Shapes
        If shp.Name = "lblGreen" Then shp.Visible = False
        If shp.Name = "lblRed" Then shp.Visible = True
    Next shp
    
    WS.Unprotect "password"
    WS.Range("AW10:BA10").Value = "80"
    WS.Range("S10:W10").Value = "80"
    WS.Range("AW10:BA10").Locked = True
    WS.Range("S10:W10").Locked = True
    WS.Protect "password"
    
Next WS

End Sub

 

[OfficeUser]

The shapes and ranges need to be changed on each sheet in the workbook. I am curious as to why the code will lock one range but not the other, seems very odd to me.