ExcelBanter - View Single Post - password protect multiple worksheets in excel

Ken Johnson

wrote:
Can anyone help me, I am trying to find a vba code that will allow mw
to select a number of excel worksheets to apply protection to in one
go.
I have a workbook with 50 w/sheets and i only need to protect 30 of
them can i get a code that will allow me to choose the relevant sheets
that I wish to protect without all of the worksheets in the workbook
being protected.
I used to have a macro that did this but my version of excel was
updated and I lost the macro on my personal.xls.

Can anyone help!

The following macros worked for me. Edit "password" to suit your
needs...

To protect some sheets first use Shift-Click Sheet tabs to group those
sheets for protection then run the following macro...

Public Sub ProtectSelectedSheets()
Dim Sht As Worksheet
Dim ncProtect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncProtect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncProtect
Sht.Protect "password"
Next Sht
End Sub

To Unprotect some protected sheets first use Shift-Click Sheet tabs of
sheets to group them for removal of protection then run the following
macro...

Public Sub UnprotectSelectedSheets()
Dim Sht As Worksheet
Dim ncUnprotect As New Collection
For Each Sht In ActiveWindow.SelectedSheets
ncUnprotect.Add Item:=Sht
Next Sht
Worksheets(1).Select
For Each Sht In ncUnprotect
Sht.Unprotect "password"
Next Sht
End Sub

Ken Johnson