- Mar. 09, 2014
|Example files with this article:|
We all know that passwords in Excel are not very safe, although security is improved from version to version. Here is a short article on a nifty trick whereby you can have changing passwords in Excel. Each time, the password asked from the user is (or can be) different (and only the user knows what kind of password is required). The passwords that I discuss here are your own custom passwords within an Excel file - we are not talking about the password that you can use to block (write) access to a file.
The trick is to use for example the current time as the password: if we are now 7:42 PM, then the password would be 0742. I guess that many of us have a clock in the lower right corner of their screen, so in that case only shifting your eyes to the right would be enough to know the password required.
A code sample would be as follows.
Sub ChangingPassword()' Wim Gielis ' https://www.wimgielis.comDim dtNow As Date Dim sPassword_Required As String Dim sPassword_Given As String dtNow = Now sPassword_Required = Format(dtNow, "hhmm") sPassword_Given = InputBox("Please enter your password:") If sPassword_Given = sPassword_Required Then MsgBox "Your password is correct" Else MsgBox "Your password is incorrect" End IfEnd Sub
You can see in the code that I use the functions Format and Now to return the current time as text (a variable declared as String). This is the required password. The password asked from the user, is stored in a variable as well. Lastly, a very simple check is done to see if both passwords match. Please feel free to modify the code and use it to your own benefit. You can include the current day or month, you could even include the level of seconds (and adapt the password match test to suit).
If you use the level of the minute, there is always the possibility that the macro procedure is started just before a minute stops, and when the user starts typing the password, the new minute started. To allow for that possibility, you might want to allow 5 seconds:
Sub ChangingPassword_AllowTimeInterval()' Wim Gielis ' https://www.wimgielis.comDim dtNow As Date Dim sPassword_Required As String Dim sPassword_Given As String dtNow = Now sPassword_Required = Format(dtNow, "hhmm") sPassword_Given = InputBox("Please enter your password:") If (sPassword_Given = sPassword_Required) Or _ ((sPassword_Given = Format(DateAdd("n", 1, dtNow), "hhmm")) And (Second(Now) <= 5)) Then MsgBox "Your password is correct" Else MsgBox "Your password is incorrect" End IfEnd Sub
Comparing the first code and the last code, the only change is in the extended IF test: now, it is enough to have the minutes correct, OR you can offset by 1 minute if you are within the first 5 seconds of the next minute. See the 5 at the end of the line. DateAdd is a convenient way to add up 1 minute to an existing date/time variable.
In summary, this article showed you a nice trick to ask a more or less random password. The user only needs to look at the system clock to know the password.