Auto Protect when closing

[Dianna Zueff]

Hi,

I would like to use a Macro to auto password protect all worksheets AND the workbook when exiting my document. I have about 10 different tabs all need to protected upon exit but column B need to remain as unprotected (I have that part working) i just don't want to have enter the code in each worksheet when I'm done my edits.

PLEASE HELP!
Cheers
DREX

 

[Chihiro]

Something like this in your Workbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    ws.Protect "password"
Next ws

ThisWorkbook.Protect "password", True

ThisWorkbook.Save

End Sub

 

[Dianna Zueff]

This did not work. When I close and open the workbook again the sheets are still unprotected. See sheet 1 (BH). I copied and pasted above codes and changed "password" in both locations to the password I want to use. Please help!

I have attached a sample worksheet. The password for the worksheets is "test" and the password for the workbook protection is "chandoo". There are 2 hidden tabs (HO and BLs) that is why I need both the worksheet and workbook protections

 

[Chihiro]

Ok, go to VBA module. Go to Project pane.

Find "ThisWorkbook" and double click on it, and paste the code there.

You currently have code in "Sheet1" module.

 

[Dianna Zueff]

DOH!
THANK YOU...that worked beautifully
Cheers!

 

[Dianna Zueff]

on a related note...in addition to the above codes ... is there a way to also auto hide the sheets named HO and BLs and protect workbook upon closing the document. So that there is no chance of forgetting and exposing the data in those sheets to eyes that shouldn't see them?

Thanks again.

 

[Chihiro]

Sure replace previous code with something like...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "HO" Or ws.Name = "BLs" Then
        If ws.Visible <> xlSheetHidden Then ws.Visible = xlSheetHidden
        ws.Protect "test"
    Else
        ws.Protect "test"
    End If
Next ws

ThisWorkbook.Protect "chandoo", True

ThisWorkbook.Save

End Sub

But do note that if user has intermediate knowledge of Excel/VBA. It's not hard at all to break the password and/or protection.

 

[Dianna Zueff]

YOU ARE AWESOME!!!!

I am aware and have advised my leadership team, that these protections and passwords are NOT security methods but only deterrents... I have also password protected the VBA code to add another layer of protection.

Thanks again for all your help!!
Cheers
D

 

[Tumee]

Hello Chandoo,
How can i auto protect my sheet along with specific features like enabled sort and filter when closing excel?

 

[vletm]

Tumee
You should open a new thread as written in Forum Rules.
This thread is closed now.