Sheet Protection Problem

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hello All,
Using Excel XP. I have 100 worksheets in a workbook where I have a
range in each one that I have the cells protected. I am using VB code to
protect and unprotect the worksheets. (see below).
The problem that I am having is after unprotecting the sheets and then
protecting them again using the VB code I can go to the protected cells and
change the data (which I don't what to happen). I looked at the Protect
Sheet Box where it says "Allow all users of the worksheet to:..." the
first box in the group, Select Locked Cells is checked. I don't want this
box checked, I just want users to select unlocked cells. But in order to do
this I have to go to each individual worksheet and uncheck the Select Locked
Cells box. Is there any way I can add this command to the following VB code
so that I can have it changed on all the worksheets at one time. Thank you.
Mike

This is the VB code that I am using to protect and unprotect all the sheets
in the workbook:

Sub protect_sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Protect password:="123"

Next ws

End Sub



Sub unprotect_sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Unprotect password:="123"

Next ws

End Sub
 
try doing it this way
ws.EnableSelection = xlUnlockedCells
ws.Protect password:="123"
 
Mike

Something like

Sub wsprotect()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect Password:="123"
ws.EnableSelection = xlUnlockedCells
Next ws
End Sub

Should do it

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Back
Top