Prev: pivot table to MS Access query
Next: Sub to check and report any formula returned error (eg: #REF!)
From: Max on 5 Feb 2010 12:15
Looking for sub to protect/unprotect a list of sheets via using their
codenames, eg:
Sheet8
Sheet97
etc

Thanks




From: Ryan H on 5 Feb 2010 13:51
After reading your post I assume you want to protect and unprotect a random
set of sheets. This code should work well for you. Just list the sheet
names in the With Statement to fit your application. Hope this helps! If
so, let me know, click "YES" below.

Sub ProtectSheets()

Dim colSheets As Collection
Dim wks As Worksheet

' build collection of sheets
Set colSheets = New Collection
With colSheets
.Add Sheets("Sheet8")
.Add Sheets("Sheet97")
End With

' protect worksheets in collection
For Each wks In colSheets
wks.Protect "Password Goes Here"
Next wks

End Sub


Sub UnProtectSheets()

Dim colSheets As Collection
Dim wks As Worksheet

' build collection of sheets
Set colSheets = New Collection
With colSheets
.Add Sheets("Sheet8")
.Add Sheets("Sheet97")
End With

' protect worksheets in collection
For Each wks In colSheets
wks.Unprotect "Password Goes Here"
Next wks

End Sub

If
--
Cheers,
Ryan


"Max" wrote:

> Looking for sub to protect/unprotect a list of sheets via using their
> codenames, eg:
> Sheet8
> Sheet97
> etc
>
> Thanks
>
>
>
>
From: Max on 5 Feb 2010 16:50
Thanks, Ryan. I tweaked it a bit to refer to the actual codenames (below) and
it worked v.well. Marvellous!

.Add Sheet8
.Add Sheet97

 | 
Pages: 1
Prev: pivot table to MS Access query
Next: Sub to check and report any formula returned error (eg: #REF!)