Sheet Protection Problem

[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

 

[Don Guillett]

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

 

[Nick Hodge]

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)

 

[Mike]

thanks Don for quick reponse. Works great!!,
Mike

 

[Don Guillett]

glad to help