Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
protecting range with macro
I have a macro button that opens up multiple workbooks. I need it to
also protect a range of columns IE Column A - Column F. Each workbook can have different number of rows so protecting the whole column is probably easiest. The sheet within that workbooks can have different names (not sheet1 but other names in it, not sure if that makes a difference. When I run this code all of the cells are protected not the range I specify. What am I doing wrong????? Here is my code Sub Button1_Click() Dim sPath As String, sName As String Dim bk As Workbook Dim ws As Worksheet sPath = "C:\test\" sName = Dir(sPath & "*.xls") Do While sName < "" Set bk = Workbooks.Open(sPath & sName) For Each ws In bk.Worksheets ws.Range("A1:F10").Locked = True ws.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True Next ws sName = Dir Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
protecting range with macro
Hi Corkster,
The default condition is that all cells are locked. Try unlocking all cells before locking the designated range and applying protection. Perhaps, therefore, replace: For Each ws In bk.Worksheets ws.Range("A1:F10").Locked = True ws.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True Next ws with: For Each ws In bk.Worksheets With ws .Cells.Locked = False .Range("A:F").Locked = True .Protect Password:="test", _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True End With Next ws --- Regards, Norman "corkster" wrote in message ps.com... I have a macro button that opens up multiple workbooks. I need it to also protect a range of columns IE Column A - Column F. Each workbook can have different number of rows so protecting the whole column is probably easiest. The sheet within that workbooks can have different names (not sheet1 but other names in it, not sure if that makes a difference. When I run this code all of the cells are protected not the range I specify. What am I doing wrong????? Here is my code Sub Button1_Click() Dim sPath As String, sName As String Dim bk As Workbook Dim ws As Worksheet sPath = "C:\test\" sName = Dir(sPath & "*.xls") Do While sName < "" Set bk = Workbooks.Open(sPath & sName) For Each ws In bk.Worksheets ws.Range("A1:F10").Locked = True ws.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True Next ws sName = Dir Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
protecting range with macro
On May 2, 11:59 pm, "Norman Jones"
wrote: Hi Corkster, The default condition is that all cells are locked. Try unlocking all cells before locking the designated range and applying protection. Perhaps, therefore, replace: For Each ws In bk.Worksheets ws.Range("A1:F10").Locked = True ws.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True Next ws with: For Each ws In bk.Worksheets With ws .Cells.Locked = False .Range("A:F").Locked = True .Protect Password:="test", _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True End With Next ws --- Regards, Norman "corkster" wrote in message ps.com... I have a macro button that opens up multiple workbooks. I need it to also protect a range of columns IE Column A - Column F. Each workbook can have different number of rows so protecting the whole column is probably easiest. The sheet within that workbooks can have different names (not sheet1 but other names in it, not sure if that makes a difference. When I run this code all of the cells are protected not the range I specify. What am I doing wrong????? Here is my code Sub Button1_Click() Dim sPath As String, sName As String Dim bk As Workbook Dim ws As Worksheet sPath = "C:\test\" sName = Dir(sPath & "*.xls") Do While sName < "" Set bk = Workbooks.Open(sPath & sName) For Each ws In bk.Worksheets ws.Range("A1:F10").Locked = True ws.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True Next ws sName = Dir Loop End Sub- Hide quoted text - - Show quoted text - Perfect, thanks that worked just fine. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting the Print Range. | Excel Discussion (Misc queries) | |||
Protecting a Range of Specific Cell/s | Excel Discussion (Misc queries) | |||
Protecting a Range in a list | Excel Programming | |||
Protecting a Used Range | Excel Programming | |||
Protecting Range Names | Excel Discussion (Misc queries) |