We help IT Professionals succeed at work.

Dynamic password in Excel VBA

shampouya
shampouya asked
on
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

Comment
Watch Question

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)
Top Expert 2015

Commented:
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...
Top Expert 2014

Commented:
Have you considered using a GUID for your password?
Example:
strGUID = Mid(CreateObject("scriptlet.typelib").guid, 2, 36)

Open in new window