How the excel work sheet protection password can be retained while closing it with out manually protecting it?

Question

How the excel work sheet protection password can be retained while closing it with out manually protecting it?

steps

1. I have created an excel sheet and protected with a password. So the sheet is now read-only.

2. I opened the same sheet and edited that entering the password.

3. If i save and close excel sheet with out protecting manually, next time any one who opens the same excel sheet can modify as it is not read only.

My requirement is when i close the sheet after saving . The password i set for protection initially should be retained. So that the sheet will be read only all the time.

Answer 1

Alt+F11, double click module(ThisWorkBook) on the left and then put the following code to the blank area on the right, then click save button on the vba editor.

Basically, when the workbook close, the code routine will activate,

1st line protect sheet1(tab name) with object content and scenarios checked, if any need uncheck set it to false, password is myPwd, if more sheet need protection, duplicate the line and change its attributes accordingly.

2nd line protect this workbook structure, but not windows, password is myPwd2 .

3rd line save the workbook

4th line it disable the prompt sometime that appear asking whether you want to save the file, cause it just did above.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

   Worksheets( "Sheet1" ).Protect password:="myPwd", Contents:=True, DrawingObjects:=True, Scenarios:=True

   ThisWorkbook.Protect password:="myPwd2", structure:=True, Windows:=False

   ThisWorkbook.Save

   ThisWorkbook.Saved = True

End Sub

Please contact for more info.