protecting range with macro

  • Thread starter Thread starter corkster
  • Start date Start date
C

corkster

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
 
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
Click to expand...

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
 
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:



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










- Show quoted text -
Click to expand...

Perfect, thanks that worked just fine.
 
Back
Top