• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

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

0
shampouya
Asked:
shampouya
1 Solution
 
als315Commented:
In your case you should remember weekday when password was set (you can, of course, look at file properties and take date there).
dateOfWeek = Weekday (Now)
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
aikimarkCommented:
Have you considered using a GUID for your password?
Example:
strGUID = Mid(CreateObject("scriptlet.typelib").guid, 2, 36)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now