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

chinga

New Member
Joined
Jun 9, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 9, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

rollis13

Well-known Member
Joined
Jul 30, 2012
Messages
1,027
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,176,264
Messages
5,902,231
Members
434,952
Latest member
AlanN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top