Strange error when changing locked status of cells
 

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

Strange error when changing locked status of cells
 

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
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


" wrote:

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

Strange error when changing locked status of cells
 

Hi Paul

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

Regards,
Per

skrev i meddelelsen
...
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

Strange error when changing locked status of cells
 

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

On Jul 3, 4:43*pm, John Bundy (remove) wrote:
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
--
-John
Please rate when your question is answered to help us and others know what
is helpful.



" wrote:
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- Hide quoted text -

- Show quoted text -

Strange error when changing locked status of cells
 

lol that makes sense! Overthought it myself.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Per Jessen" wrote:

Hi Paul

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

Regards,
Per

skrev i meddelelsen
...
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