Excel Pop Up Box to Unprotect All Worksheets When First Opening Excel

[chinga]

Hi Im trying to make a pop up box automatically bring itself up when opening excel so i can unprotect all the worksheets in one go.
Im using the below code to automatically Protect all my worksheets when i close the excel, but want something that prompts a password when re-opening.

I would just normally lock the file in the Save As, Tools etc option but the file is part of a sharepoint and this way really mucks up the file.

I need a macro to help with this issue.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Update by Extendoffice 2018/1/24
Dim xSheet As Worksheet
Dim xPsw As String
xPsw = "PASSWORD"
For Each xSheet In Worksheets
xSheet.Protect xPsw
Next
End Sub

 

[chinga]

In the Workbook_Open before asking the password set the focus on an empty sheet, and, just to be sure, when saving use event Workbook_BeforeClose to do the same.
All you need is a Worksheets("Sheet2").Select (adjust sheet name as needed)

Also is there a way that if someone puts in the wrong password it stays locked to the blank sheet?

 

[rollis13]

Hi rollis13 thanks so much for all this, its working a treat. Can i use the same macro in WORD?

Let's say no because Word doen't have sheets but other solutions came be found in web.

Also is there a way that if someone puts in the wrong password it stays locked to the blank sheet?

Hide the sheets (except one, the blank one) before closing, add xSheet.Visible = False in a loop in Workbook_BeforeClose event macro.
And on opening unhide only if given the right password, add xSheet.Visible = True just after xSheet.Unprotect xPsw in the WorkBook_Open event macro.