Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and Unlock Cells
Hello All,
I am trying to get my sheets to work as follows: Unprotect the worksheet (with password) Protect cell range B16,D45 Unprotect cell range N16,N45 Protect sheet (with password) Then move to the next sheet in the Workbook and repeat the process. There are 20+ Worksheets in the Workbook. Regards Peter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and Unlock Cells
One way:
Const sPWORD As String = "drowssap" Dim ws As Worksheet For Each ws In Worksheets With ws .Unprotect Password:=sPWORD .Range("B16:D45").Locked = True .Range("N16:N45").Locked = False .Protect Password:=sPWORD End With Next ws In article , "Peter" wrote: I am trying to get my sheets to work as follows: Unprotect the worksheet (with password) Protect cell range B16,D45 Unprotect cell range N16,N45 Protect sheet (with password) Then move to the next sheet in the Workbook and repeat the process. There are 20+ Worksheets in the Workbook. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and Unlock Cells
Hello JE,
Thanks for the info... I have tried the code you suggested, but to no avail....Yet. When I reopen the worksheet saved when exiting, i get a Run TIme Error 1004 - Unable to set the locked property of the range class error, and the breakpoint rests on the line .Range("B16:D45").Locked = True I have already added the "Trust Access To Visual Basic Project" as it suggested..still no luck. Any suggestions as to what I may have done wrong? Regards Peter "JE McGimpsey" wrote: One way: Const sPWORD As String = "drowssap" Dim ws As Worksheet For Each ws In Worksheets With ws .Unprotect Password:=sPWORD .Range("B16:D45").Locked = True .Range("N16:N45").Locked = False .Protect Password:=sPWORD End With Next ws In article , "Peter" wrote: I am trying to get my sheets to work as follows: Unprotect the worksheet (with password) Protect cell range B16,D45 Unprotect cell range N16,N45 Protect sheet (with password) Then move to the next sheet in the Workbook and repeat the process. There are 20+ Worksheets in the Workbook. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and Unlock Cells
Assuming you are doing this in the Workbook_Open event, Try
Private Sub Workbook_Open() Const sPWORD As String = "drowssap" Dim aws as Worksheet Dim ws As Worksheet Application.ScreenUpdating = False set aws = ThisWorkbook.Activesheet For Each ws In ThisWorkbook.Worksheets With ws .Activate .Unprotect Password:=sPWORD .Range("B16:D45").Locked = True .Range("N16:N45").Locked = False .Protect Password:=sPWORD End With Next ws aws.Activate Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy "Peter" wrote in message ... Hello JE, Thanks for the info... I have tried the code you suggested, but to no avail....Yet. When I reopen the worksheet saved when exiting, i get a Run TIme Error 1004 - Unable to set the locked property of the range class error, and the breakpoint rests on the line .Range("B16:D45").Locked = True I have already added the "Trust Access To Visual Basic Project" as it suggested..still no luck. Any suggestions as to what I may have done wrong? Regards Peter "JE McGimpsey" wrote: One way: Const sPWORD As String = "drowssap" Dim ws As Worksheet For Each ws In Worksheets With ws .Unprotect Password:=sPWORD .Range("B16:D45").Locked = True .Range("N16:N45").Locked = False .Protect Password:=sPWORD End With Next ws In article , "Peter" wrote: I am trying to get my sheets to work as follows: Unprotect the worksheet (with password) Protect cell range B16,D45 Unprotect cell range N16,N45 Protect sheet (with password) Then move to the next sheet in the Workbook and repeat the process. There are 20+ Worksheets in the Workbook. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and Unlock Cells
Hello Tom,
Thanks for the code. It's doing the exact same thing though. I have removed all other macro's from my workbook, and it's still the same. Any idea's why are welcome.. Regards Peter "Tom Ogilvy" wrote: Assuming you are doing this in the Workbook_Open event, Try Private Sub Workbook_Open() Const sPWORD As String = "drowssap" Dim aws as Worksheet Dim ws As Worksheet Application.ScreenUpdating = False set aws = ThisWorkbook.Activesheet For Each ws In ThisWorkbook.Worksheets With ws .Activate .Unprotect Password:=sPWORD .Range("B16:D45").Locked = True .Range("N16:N45").Locked = False .Protect Password:=sPWORD End With Next ws aws.Activate Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy "Peter" wrote in message ... Hello JE, Thanks for the info... I have tried the code you suggested, but to no avail....Yet. When I reopen the worksheet saved when exiting, i get a Run TIme Error 1004 - Unable to set the locked property of the range class error, and the breakpoint rests on the line .Range("B16:D45").Locked = True I have already added the "Trust Access To Visual Basic Project" as it suggested..still no luck. Any suggestions as to what I may have done wrong? Regards Peter "JE McGimpsey" wrote: One way: Const sPWORD As String = "drowssap" Dim ws As Worksheet For Each ws In Worksheets With ws .Unprotect Password:=sPWORD .Range("B16:D45").Locked = True .Range("N16:N45").Locked = False .Protect Password:=sPWORD End With Next ws In article , "Peter" wrote: I am trying to get my sheets to work as follows: Unprotect the worksheet (with password) Protect cell range B16,D45 Unprotect cell range N16,N45 Protect sheet (with password) Then move to the next sheet in the Workbook and repeat the process. There are 20+ Worksheets in the Workbook. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock and Unlock Cells
Try it in a new workbook. The code works fine for me.
-- Regards, Tom Ogilvy "Peter" wrote in message ... Hello Tom, Thanks for the code. It's doing the exact same thing though. I have removed all other macro's from my workbook, and it's still the same. Any idea's why are welcome.. Regards Peter "Tom Ogilvy" wrote: Assuming you are doing this in the Workbook_Open event, Try Private Sub Workbook_Open() Const sPWORD As String = "drowssap" Dim aws as Worksheet Dim ws As Worksheet Application.ScreenUpdating = False set aws = ThisWorkbook.Activesheet For Each ws In ThisWorkbook.Worksheets With ws .Activate .Unprotect Password:=sPWORD .Range("B16:D45").Locked = True .Range("N16:N45").Locked = False .Protect Password:=sPWORD End With Next ws aws.Activate Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy "Peter" wrote in message ... Hello JE, Thanks for the info... I have tried the code you suggested, but to no avail....Yet. When I reopen the worksheet saved when exiting, i get a Run TIme Error 1004 - Unable to set the locked property of the range class error, and the breakpoint rests on the line .Range("B16:D45").Locked = True I have already added the "Trust Access To Visual Basic Project" as it suggested..still no luck. Any suggestions as to what I may have done wrong? Regards Peter "JE McGimpsey" wrote: One way: Const sPWORD As String = "drowssap" Dim ws As Worksheet For Each ws In Worksheets With ws .Unprotect Password:=sPWORD .Range("B16:D45").Locked = True .Range("N16:N45").Locked = False .Protect Password:=sPWORD End With Next ws In article , "Peter" wrote: I am trying to get my sheets to work as follows: Unprotect the worksheet (with password) Protect cell range B16,D45 Unprotect cell range N16,N45 Protect sheet (with password) Then move to the next sheet in the Workbook and repeat the process. There are 20+ Worksheets in the Workbook. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Check Box to Lock/Unlock Cells | Excel Discussion (Misc queries) | |||
Lock or Unlock Range of Cells on Worksheet_Change Event | Excel Worksheet Functions | |||
Protection - Allow Group/Ungroup but lock / unlock some cells | Excel Discussion (Misc queries) | |||
Lock/Unlock cells | Excel Worksheet Functions | |||
Lock and Unlock cells using VBA | Excel Discussion (Misc queries) |