Strange error when changing locked status of cells

[paul.robinson]

Hi
Trying to answer a post for someone else and have come down to this.
I have a sheet called InputDetails. I save the file which calls the
sub rspProtectInputSheet from the BeforeSave event.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Call rspProtectInputSheet
End Sub

Public Sub rspProtectInputSheet()
Dim password As String 'This line of code is optional
password = "xxxx"
Worksheets("InputDetails").Cells.Locked = True
Worksheets("InputDetails").Protect password, contents:=True, _
Userinterfaceonly:=True, AllowFormattingColumns:=False
End Sub

I now have InputDetails password protected. I can save the file again
without a problem. I can unprotect the sheet, edit it and save and
again the sheet is protected.
Now I close the file and reopen it. When I save I now get an error
"Unable to Set the Locked Property of the Range class" with the line

Worksheets("InputDetails").Cells.Locked = True

highlighted.
Any ideas why this happens??

regards
Paul

 

[John Bundy]

You are attempting to lock something that is already locked, just throw a
check in for it.

Public Sub rspProtectInputSheet()
Dim password As String 'This line of code is optional
password = "xxxx"
If Worksheets("InputDetails").Cells.Locked = False Then
Worksheets("InputDetails").Cells.Locked = True
End If
Worksheets("InputDetails").Protect password, contents:=True, _
Userinterfaceonly:=True, AllowFormattingColumns:=False
End Sub

 

[Per Jessen]

Hi Paul

Your worksheet is protected, so you can't change the behaviour of the cells.

Regards,
Per

 

[paul.robinson]

Thanks, I'll do that.
But why can I repeatedly save the file, where the cells are locked and
the sheet protected after the first save? The error only crops up when
I close it, reopen and then save.
Not a practical problem as you say, but odd. I'm using Excel 2003 with
XP Professional.
regards
Paul

 

[John Bundy]

lol that makes sense! Overthought it myself.