protecting range with macro

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
 
N

Norman Jones

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
 
C

corkster

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 -

Perfect, thanks that worked just fine.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top