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

 

[rollis13]

Could be a solution, to be pasted in ThisWorkBook's module:

Option Explicit
Private Sub Workbook_Open()
    Dim xSheet As Worksheet
    Dim xPsw   As String
    Dim answer As String
    answer = MsgBox("Do you want to unprotect all your sheets ?", vbYesNo)
    If answer = vbYes Then
        xPsw = "PASSWORD"
        For Each xSheet In Worksheets
            xSheet.Unprotect xPsw
        Next
        MsgBox "Done!"
    End If
End Sub

 

[chinga]

Could be a solution, to be pasted in ThisWorkBook's module:

Option Explicit
Private Sub Workbook_Open()
    Dim xSheet As Worksheet
    Dim xPsw   As String
    Dim answer As String
    answer = MsgBox("Do you want to unprotect all your sheets ?", vbYesNo)
    If answer = vbYes Then
        xPsw = "PASSWORD"
        For Each xSheet In Worksheets
            xSheet.Unprotect xPsw
        Next
        MsgBox "Done!"
    End If
End Sub

Hi rollis13 that works perfect.

Is there a way to bring up a password box instead of a yes or no message box?

Thanks so much

 

[rollis13]

How about this:

Option Explicit
Private Sub Workbook_Open()
    Dim xSheet As Worksheet
    Dim xPsw   As String
    Dim answer As String
    xPsw = "PASSWORD"
    answer = InputBox("Input password if you want to unprotect" & vbLf & "all your sheets then press OK.")
    If answer = xPsw Then
        For Each xSheet In Worksheets
            xSheet.Unprotect xPsw
        Next
        MsgBox "Done!"
    End If
End Sub

but you could have done that by yourself .

 

[chinga]

How about this:

Option Explicit
Private Sub Workbook_Open()
    Dim xSheet As Worksheet
    Dim xPsw   As String
    Dim answer As String
    xPsw = "PASSWORD"
    answer = InputBox("Input password if you want to unprotect" & vbLf & "all your sheets then press OK.")
    If answer = xPsw Then
        For Each xSheet In Worksheets
            xSheet.Unprotect xPsw
        Next
        MsgBox "Done!"
    End If
End Sub

but you could have done that by yourself .

Thanks so much for that worked a treat. Sorry im only learning at the moment lol how does one start learning about VBA script.

 

[rollis13]

Always set new goals and keep working on them (with or without help), sooner or later it gets into your head .
Thanks for the positive feedback, glad having been of some help.

 

[chinga]

Always set new goals and keep working on them (with or without help), sooner or later it gets into your head .
Thanks for the positive feedback, glad having been of some help.

Cheers thanks for that ill have to get started on some basic code.

Also I was wondering with the above code, is it possible to make it that if someone puts the wrong password in, the screen goes blank/black so they cant see what the sheets say?

 

[rollis13]

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)

 

[chinga]

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

 

[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)

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