CODEWITHSUNDEEP
excelvba
user5700405
user5700405

Reputation: 43

ThisWorkbook.Protect and Unprotect in Close and Open events

I want to unprotect a workbook immediately upon opening it, if it is protected the last time it was closed. I place the following code at the very beginning of the Open event:

If ThisWorkbook.ProtectStructure = True Then
    Call ThisWorkbook.Unprotect("openpassword")
End If

Similarly, I want to protect the workbook right before it is closed. So I place the following codes at the very end of the beforeClose event:

If ThisWorkbook.ProtectStructure = False Then
    Call ThisWorkbook.Protect("openpassword",True, False)
End If

ThisWorkbook.Save

Neither of those two works! Meaning, if a workbook is already protected upon opening, none of the code in Open event is executed. Not even the unprotect call!

Similarly, in the beforeClose event, all codes before the protection part are executed, except the protection part and any codes following it. I have confirmed that by placing it in different places in the beforeClose event.

Anyone could help me with this?

Additional explanation why I want to do this: the workbook is being shared among users with different level of authorized access. Most users face a protected workbook when allowed to work on only certain sheets. Hence, depending on who uses it last, the workbook could be closed in a protected state or unprotected state. If it is unprotected during the usage, I need to protect it immediately upon closing, so that the next unauthorized users will open a protected workbook.

Upvotes: 0

Views: 2241

Answers (2)

Kathara
Kathara

Reputation: 1290

Try the following instead:

Dim sht As Worksheet
For Each sht in ActiveWorkbook.worksheets
    If sht.Protection = True Then
        sht.Unprotect("openpassword")
    End If
Next sht

And the same at the end:

Dim sht as Worksheet
For Each sht in ActiveWorkbook.Worksheets
    If sht.Protection = False Then
        sht.Protect ("openpassword", True, False)
    End If
Next sht

In excel - as far as I know - it isn't possible to protect the whole workbook directly but instead you have to protect every sheet.

You can at least try it, if it works I'm happy too :)

CU Kath

Upvotes: 0

PaulG
PaulG

Reputation: 1189

Can you check that the events are actually firing? Try putting a Stop at the beginning of each event handler to see they are called. If the code isn't executing, what happens if you save it as a trusted document?

Just a few things: Why don't you force a protection when closing for everyone, and only allow unprotecting when opening for certain people?

How are you determining which users can unprotect the workbook?

You're not saving the workbook as a shared workbook, right? It's a normal workbook, but accessed by different users. Shared workbooks are a bit funny with protection.

Upvotes: 0

Related Questions