How to protect a specific workbook only in Excel VBA

Question

What is the best way of protecting a specific Excel workbook?

I have an inherited script that includes the following common lines at the end:

ActiveSheet.Protect "my-password"
ActiveWorkbook.Protect "my-password"

However, I've noticed that as the script can take a few minutes to run users often switch to a new unrelated workbook whilst it solves - whatever else they are working on. The password protection is then inherited by the unrelated workbook upon the completion of the macro - since whatever other Excel file the user is working within is now "Active" (presumably? this is my reading of the problem).

The above script is in a workbook that can be renamed to whatever the user chooses, and be saved in any number of directories. How can I ensure that only the original excel file is locked/unlocked by the Macro, when other workbooks are in use?

I am sure there are many ways to do this, but which is the most foolproof method?

NOTE: using office 365

@Dean that is probably the syntax I need, will test. Cheers. So simple... — Petrichor
– Petrichor, Commented Aug 16, 2019 at 14:11
You might want to also store the ActiveSheet to a worksheet object in the event your users are changing workbooks., You will then be able to still reference that "ActiveSheet" regardless if they change workbooks. — DDV
– DDV, Commented Aug 16, 2019 at 14:20
Hmmm, unfortunately I get an "object required" error when using ThisSheet.Protect — Petrichor
– Petrichor, Commented Aug 16, 2019 at 14:21
Yeah ThisSheet does not exist. You will be able to use ThisWorkbook as a Workbook object. To use a Worksheet object I would recommend reading my comment above yours. — DDV
– DDV, Commented Aug 16, 2019 at 14:22

Petrichor · Accepted Answer · 2019-08-16 15:31:06Z

0

Thanks Dean's answers in the comments:

Early in the code (and in Worksheet_Change if appropriate) enter the following to define your sheet as an object (named default_ws in my case):

Set default_ws = ActiveSheet

When you are ready to lock your sheet or workbook you can then use:

default_ws.Protect "password-here" 'protect your sheet
ThisWorkbook.Protect "password-here" 'protect your workbook

Also note:

You could also define your workbook as an object as follows if desired:

Set default_wb = ActiveWorkbook

edited Aug 16, 2019 at 15:31

answered Aug 16, 2019 at 15:13

Petrichor

1,0851 gold badge12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to protect a specific workbook only in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related