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

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
140
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Aug 11, 2006
Messages
140
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
140
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Aug 11, 2006
Messages
140
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 10, 2017
Messages
2,020
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,143,690
Messages
5,720,311
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top