Using VBA code to protect a workbook with dynamic password and unprotecting it when user enters a password which matches any within a range.

[kelly mort]

Hi,
I want to use vba to dynamically password protect my workbook.

I have a table that contains the list of passwords that I want to use.

I want a code that will password protect the workbook using one of the password from the list.

Then when I want to unprotect the workbook, if the entered password can be found among the list, then unprotect the workbook.

If the above is possible, can someone some me how?

Thanks in advance.
Kelly

 

[Dermot]

I'm doubtful that your approach will work.

This is because in order to run the code, the workbook must open fully. A user can prevent your code running when it opens, by simply holding down the Shift key, so they can open it and see everything without logging in (unless the contents are securely hidden or encrypted in some way). They may not be able to change anything if you've locked the worksheets, but they can still see everything. They may also be able to find your table of passwords, unless it is very cunningly hidden. This is not good.

I can't see a good reason for having multiple passwords - can you clarify this?

 

[kelly mort]

In that case then I have to forget about the workbook protection.

Currently, I hide all tabs and also the ribbon(which I am still looking for a better way to tackle).

Which means regular users can't access the data from the sheet even if the code does not run when the workbook opens.

When the workbook opens, I have to login using a userform which verifies my credentials (from a hidden sheet).

This hidden sheet contains the users and their credentials.

So I was thinking that using dynamic password to protect the workbook could help give some form of double layer of protection.

 

[Dermot]

I could unhide all your sheets using a couple of lines of code
Even if the workbook structure was protected, I could break that password easily (internal sheet encryption is very weak), and then unhide the sheets
or else I could write code that simply copies the contents of the sheets to another workbook
I can also break the protection on your code, to see how it works and bypass it

So how secure it is, will depend on the skills of your users (and their friends). Most of the time, you will be OK, but if it really needs to be secure, using a homebrew password is not enough

Is it really important, or are you just trying it out?

 

[kelly mort]

So if I want a better security, what should be the best option to use for protection?

 

[Dermot]

So if I want a better security, what should be the best option to use for protection?

The best protection is the main workbook password, that is very secure, and can only be broken by brute force. There are websites that will do it for a price, but it will take them a while.

I have encrypted worksheet contents securely in the past, and required a password to decrypt them, using secure AES encryption. This works, but you can only have one password, unless you use public key encryption, which is another level of complexity. (I have also encrypted individual rows with different passwords in a situation where each row belonged to one person, and only they had the special password for their row, but that is a bit different).

Above all, if you want to take security really seriously, you have to assume that all your code can be read, and changed, and that an attacker can see everything in your workbook, hidden or not. My example of encrypting workbook contents was secure, because if an attacker gave the wrong password, they got garbage, but if you are using the password as a key simply to open up your workbook, it can be bypassed and the contents read.

So how far you need to go depends on what you are protecting, to what extent that varies for different users, and how critical it is to protect it.

But Excel is inherently unsafe, because it is the most flexible business tool, which means there are many ways to do most things - including dismantling your security.

 

[kelly mort]

I think I am loving this already!

As a newbie to this encryption stuffs, with the AES encryption you mentioned above, in order to encrypt the contents of the Worksheet(s), do you care showing me how it's done? - I can use the single password for now.

Since you said multiple password is complex, I would have to worry about that later.

 

[Dermot]

It's a fair bit of effort to provide that, so I'd need to know you really need this for work, and aren't just doing it for fun

 

[kelly mort]

It's a fair bit of effort to provide that, so I'd need to know you really need this for work, and aren't just doing it for fun

I actually need it for work. I have been looking for best ways to secure my data.

I have been using the above stated approaches but now that I have seen the threats present in those methods, I am feeling threatened to look for a safer ways.