asked on

Dynamic password in Excel VBA

I was wondering how I can protect a worksheet with a password that changes every day, assuming a macro with the code below has run. How do I get the variable dateOfWeek to return a number from 1 to 7? And is it a bad idea to make a password slightly dynamic like that?

ActiveSheet.Protect Contents:=True, DrawingObjects:=True, UserInterfaceOnly:=True, Password:="mypassword" & dateOfWeek

Open in new window

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saurabh Singh Teotia

As per me it's both good and both as you need to try 7 combinations of password to see what's the actual password but in a way your password is changing so for a normal user your sheet is protected..

Let's say if you want to make it more dyanmic without dependent on dayofweek you can use this...

ActiveSheet.Protect Contents:=True, DrawingObjects:=True, UserInterfaceOnly:=True, Password:="mypassword" & Application.WorksheetFunction.RandBetween(1, 10)

Open in new window

What rand between will do it will generate a random number between 1 to 10 even on the same day when you run the macro again so even on the same day your password will get changed...

Saurabh...

aikimark

Have you considered using a GUID for your password?
Example:

strGUID = Mid(CreateObject("scriptlet.typelib").guid, 2, 36)

Open in new window