| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| protect formulas in locked cells from changing if data is moved | Excel Discussion (Misc queries) | |||
| Having trouble changing the Locked status | Excel Programming | |||
| .xls is locked for editing. Really strange. | Excel Discussion (Misc queries) | |||
| Copy Locked Cell Status | Excel Programming | |||
| Changing the locked propery of merged cells. | Excel Programming | |||
Linear Mode
