VBA to unprotect & protect some sheets not all.

[danbates]

Hi,

I've had a look online but all I seem to find is a code to unprotect all the sheets in my workbook.

I have a workbook with 5 sheets and I was wondering how I could unprotect/protect just 3 of the sheets instead of unprotecting all 5?

The sheets I would like unprotecting/protecting are called:
PER_SHIFT
DATA
WEEKLY

Thanks

Dan

 

[Diving_Dan]

You could use something like

Private Sub Protect_Click()
    
    Sheets("PER_SHIFT").Protect
    Sheets("DATA").Protect
    Sheets("WEEKLY").Protect

End Sub

 

[danbates]

Hi Diving_Dan,

Thank you for your reply.

Sorry I forgot to mention that my sheets are password protected.

I have just managed to put this code together after searching online and it works.

Sub Unprotect_3_Sheets()
Dim SheetList As Variant
SheetList = Array("DATA", "PER_SHIFT", "WEEKLY")
For Each SheetList In ActiveWorkbook.Worksheets
SheetList.Unprotect Password:="motorola"
SheetList.Application.EnableEvents = False
Next SheetList
End Sub

Thanks
Dan

 

[dmt32]

Hi,
try following

Sub ProtectSheets(ByVal ProtectSheet As Boolean, Optional Password As String)
 Dim sh As Worksheet
 For Each sh In Worksheets(Array("PER_SHIFT", "Data", "WEEKLY"))
    If ProtectSheet Then sh.Protect Password:=Password Else sh.Unprotect Password:=Password
 Next sh
End Sub

and to call it

'protect
ProtectSheets True, "Dan"

'unprotect
ProtectSheets False, "Dan"

Dave