How to auto re-enable sheet level password protection on close?

[rageagainstjg]

Hi everyone,
I have a workbook with several sheets that is being shared on the company's OneDrive/Teams/SharePoint. I have enabled password protection on each sheet, and each sheet uses a different password. The goal is for a user to open the file, go to their sheet, enter the password used for that sheet, and start editing. However, the problem is that once a user has entered the password once for that sheet, the sheet becomes unprotected until the user manually re-enables password protection.

I would like password protection to be automatically re-enabled once the user closes the sheet so that when they open the file again, they must enter the password in order to start editing their sheet again. Is this possible?

I found a way to password protect the entire file, which will force the user to enter a password each time before they can edit the file. However, they can edit any part of the file at that point, not just their sheet. I am trying to get it to sheet-level protection that requires a password each time before editing can begin.

Any thoughts? Is this possible?

 

[tj4242]

Workbook.BeforeClose is called before the file is closed. Workbook.BeforeClose event (Excel)

In that event, you can protect the worksheets with a password using worksheet.protect. Worksheet.Protect method (Excel)

An example that I have not tested would be

Private Sub Workbook_BeforeClose(Cancel as Boolean)  
  Worksheet(2).Protect("password")
End Sub

 

[Joe4]

Workbook.BeforeClose is called before the file is closed. Workbook.BeforeClose event (Excel)

In that event, you can protect the worksheets with a password using worksheet.protect. Worksheet.Protect method (Excel)

An example that I have not tested would be

Private Sub Workbook_BeforeClose(Cancel as Boolean) 
  Worksheet(2).Protect("password")
End Sub

I would recommend adding a line after the protection, like this:

ActiveWorkbook.Save

to ensure that update gets saved before closing (otherwise, if they close without saving, I don't think that change will be saved).

 

[rageagainstjg]

I would recommend adding a line after the protection, like this:

ActiveWorkbook.Save

to ensure that update gets saved before closing (otherwise, if they close without saving, I don't think that change will be saved).

Hello! I apologize for such a silly question, but I am by no means an Excel wizard. Will running this VBA script require me to convert the .xlsx file to a .xlsm file?

 

[Joe4]

Any Excel workbook that contains VBA code must be saved as either an "xlsm" or "xlsb".
An "xlsx" file cannot contain VBA.

 

[Skyybot]

Technically you can use Macros in a .xlsx file if you have a Macro Enable Template open with Excel.

 

[Joe4]

Technically you can use Macros in a .xlsx file if you have a Macro Enable Template open with Excel.

Are you sure?
I believe that Macro Enable Templates in Excel use the .xltm extension.
See: File formats that are supported in Excel - Microsoft Support

Note that you can run macros against an xlsx file (as you can create macros that run against other files).
You just cannot save VBA code within an xlsx file (at least not that I have ever seen; try to save a file with VBA code as an xlsx file and see what happens - it doesn't let you!).

 

[Skyybot]

@Joe4, I have two .xlam files that open with each instance of Excel. Personal_Macros.xlam and xl2BB.xlam. no matter the extension.

 

[Joe4]

@Joe4, I have two .xlam files that open with each instance of Excel. Personal_Macros.xlam and xl2BB.xlam. no matter the extension.

OK, but that is not what you said - you said "xlsx" files, not "xlam" files.

Technically you can use Macros in a .xlsx file if you have a Macro Enable Template open with Excel.

And Personal Macro workbooks are not stored in "xlsx" files. Like I said, they can run against "xlsx" files, but they are not actually stored in the "xlsx" files.

That being said, what I originally said below was not completely correct, as they are also other types of Excel files, like "xlam" and "xltm", that also can contain VBA (maybe that was the point you were trying to make? If so, you are correct there, and I stand corrected on that point.)

Any Excel workbook that contains VBA code must be saved as either an "xlsm" or "xlsb".

However, this part of what I said is true.

An "xlsx" file cannot contain VBA.

If you try to save any file with VBA code as an "xlsx", you will get this message: