Protect multiple worksheets
 

Hi Folks

I have a workbook consisting of 14 worksheets. This requires users to enter
data in various cells but I dont wish them to have access to all cells.
Therefore I use Excels "Protect sheet...." feature to prevent users
modifying formulas etc. The workbook periodically has to be changed and this
means individually unprotecting each worksheet and then re-protecting them.
Is there a way to select all 14 sheets and then protect/unprotect in one hit
?

This would save me a great deal of hassel (entering the password 28 times!)
so any solution would be very welcome.

TIA

Cas.

Hi Cas

Two very useful macros for this:

Sub LockAll()
Dim wks As Worksheet
Dim Pw As String
Dim i As Long
i = 0
Pw = InputBox("Password:", "Lock all worksheets")
If StrPtr(Pw) = 0 Then Exit Sub
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Protect Pw
If Err.Number < 0 Then
i = i + 1
Err.Clear
End If
Next
If i 0 Then MsgBox i & _
" errors during this operation"
End Sub

Sub UnLockAll()
Dim wks As Worksheet
Dim Pw As String
Dim i As Long
i = 0
Pw = InputBox("Password:", "Lock all worksheets")
If StrPtr(Pw) = 0 Then Exit Sub
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect Pw
If Err.Number < 0 Then
i = i + 1
Err.Clear
End If
Next
If i 0 Then MsgBox i & _
" errors during this operation"
End Sub

HTH. Best wishes Harald

"Cas" skrev i melding
...
Hi Folks

I have a workbook consisting of 14 worksheets. This requires users to
enter data in various cells but I dont wish them to have access to all
cells. Therefore I use Excels "Protect sheet...." feature to prevent users
modifying formulas etc. The workbook periodically has to be changed and
this means individually unprotecting each worksheet and then re-protecting
them. Is there a way to select all 14 sheets and then protect/unprotect in
one hit ?

This would save me a great deal of hassel (entering the password 28
times!) so any solution would be very welcome.

TIA

Cas.

You might want to try something like this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=33

But this will protect/unprotect all sheets at once:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=142

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Cas" wrote in message
...
Hi Folks

I have a workbook consisting of 14 worksheets. This requires users to
enter
data in various cells but I dont wish them to have access to all cells.
Therefore I use Excels "Protect sheet...." feature to prevent users
modifying formulas etc. The workbook periodically has to be changed and
this
means individually unprotecting each worksheet and then re-protecting
them.
Is there a way to select all 14 sheets and then protect/unprotect in one
hit
?

This would save me a great deal of hassel (entering the password 28
times!)
so any solution would be very welcome.

TIA

Cas.

Many thanks to all who responded.
For those with a similar need -

http://www.vbaexpress.com/kb/getarticle.php?kb_id=142

Worked a treat. (I'm sure the others are fine also but this was the first
one I tried!)

Many thanks again.

"Anne Troy" wrote in message
news:46e35$42bbcab8$97c5108d$5677@allthenewsgroups .com...
You might want to try something like this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=33

But this will protect/unprotect all sheets at once:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=142

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Cas" wrote in message
...
Hi Folks

I have a workbook consisting of 14 worksheets. This requires users to
enter
data in various cells but I dont wish them to have access to all cells.
Therefore I use Excels "Protect sheet...." feature to prevent users
modifying formulas etc. The workbook periodically has to be changed and
this
means individually unprotecting each worksheet and then re-protecting
them.
Is there a way to select all 14 sheets and then protect/unprotect in one
hit
?

This would save me a great deal of hassel (entering the password 28
times!)
so any solution would be very welcome.

TIA

Cas.