Protect/unprotect a list of sheets via their codenames

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

Looking for sub to protect/unprotect a list of sheets via using their
codenames, eg:
Sheet8
Sheet97
etc

Thanks
 
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
 
Thanks, Ryan. I tweaked it a bit to refer to the actual codenames (below) and
it worked v.well. Marvellous!

.Add Sheet8
.Add Sheet97
 
Back
Top