VBA - Unprotect multiple sheets at once

[Peter Muller]

Good morning,

I have a workbook that I share with other users. Several sheets are locked, and password protected.

I am looking for code to unprotect all the locked sheets, which is password protected, when I work in the file, but instead of entering the unprotect password several times, can I just enter the password once?

Using code:

Sub UnprotectAll()

Dim S As Variant
For Each S In Array("Report", "Analysis", "Budget")
Worksheets(S).Unprotect
Next S

End Sub

 

[Fluff]

Do you want the macro to ask you for the password, or do you want it hardcoded?

 

[Peter Muller]

Do you want the macro to ask you for the password, or do you want it hardcoded?

It must ask for the password so that the other users cannot unlock it, but at the moment I have to enter the password for each protected sheet, wanting to only type it in once if possible

 

[Fluff]

Ok, how about

Sub PeterMuller()
   Dim Ws As Worksheet
   Dim Pword As String
   
   Pword = InputBox("Please enter the password")
   If Pword = "" Then Exit Sub
   
   For Each Ws In Sheets(Array("Report", "Analysis", "Budget"))
      On Error GoTo Oops
      Ws.Unprotect Pword
   Next Ws
   Exit Sub
Oops:
   MsgBox "The password used is incorrect"
End Sub

 

[Peter Muller]

Ok, how about

Sub PeterMuller()
   Dim Ws As Worksheet
   Dim Pword As String
  
   Pword = InputBox("Please enter the password")
   If Pword = "" Then Exit Sub
  
   For Each Ws In Sheets(Array("Report", "Analysis", "Budget"))
      On Error GoTo Oops
      Ws.Unprotect Pword
   Next Ws
   Exit Sub
Oops:
   MsgBox "The password used is incorrect"
End Sub

Perfect, thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.