Password Change with Macro - based on date

[Danny]

Hi,

I came up with this macro (from this NG) in my workbook.
How can I change the password based on a date?
Example:

8/20/07 = 1234
8/21/07 = 1235
8/22/07 = 1236
Etc.

Thank you.


Private Sub Workbook_BeforePrint(CANCEL As Boolean)

If Date = Now() Then
CANCEL = True

Exit Sub
End If
PassWord = InputBox("Enter Password")
If PassWord = "" Then
CANCEL = True
Exit Sub
End If
If PassWord = "1234" Then

CANCEL = False

'CountPrinting 'Limit printing even with correct date & PW, still on the works
Range("$A1").Select

Else
MsgBox " Try again ?", vb, "Wrong password !"
CANCEL = True
Exit Sub
End If
End Sub

 

[JMB]

One suggestion:


Const BaseDate As Date = #8/20/2007#
Const BasePword As Long = 1234

Sub test()
Dim pword As Long

pword = Date - BaseDate + BasePword
MsgBox pword

End Sub


The date literal is mm/dd/yyyy (U.S. short date format).

 

[Danny]

Hi JMB,

I'm sorry I did not make my self very clear. The passwords are random numbers.
In a separate worksheet on Col. A are dates in order starting witn 8/20/07.
On Col. B are corresponding random numbers.

I was wondering how I can one could write a macro for these information Col
A (date) = Cob B (password).

I will keep the macro you sent for future reference.

Thank you

 

[JMB]

You could use vlookup to identify the password for the current date:

Sub test()
Dim rngData As Range
Dim strPWord As String

Set rngData = Worksheets("Sheet1").Range("A:B")
strPWord = CStr(Application.VLookup(CLng(Date), rngData, 2, 0))
MsgBox strPWord

End Sub

you may want to pay attention to data type coercion. I made sure vlookup
returns text with CStr (although it should not be necessary since strPWord is
dimmed as string) in my example. So however you get the info from the user
and perform your comparison - I would ensure it is also text before comparing
it to whatever vlookup returns (or ensure both are numeric - but keep in mind
the Inputbox function returns text).

 

[Danny]

Hi JMB,

Say, I'd like to set up the passwords for the following dates:

8/20/07 = 1234
8/21/07 = 2345
8/22/07 = 3456
etc.

Is there a way to change the macro below If PassWord = "1234" Then
to "3456" automatically because today is 8/22/07?

I think the series of dates and corresponding passwords above should be in a
module
instead of a worksheet in the workbook.

Is there a way to do it?

Your macro below works perfectly. As usual, I keep all these macros for
future references.

Thanks again and have a nice day!

 

[Ronald Dodge]

Let's say you name the worksheet as "DatePW", and you make the worksheet
"VeryHidden" via the properties of the worksheet shown in VBA so as users
can't unhide it from the spreadsheet side, and it's in the same workbook as
the code, then the following would work out.

Dim wshDatePW as Excel.Worksheet, rngCurDate as Excel.Range, strPWord as
String
Set wshDatePW = ThisWorkbook.Worksheets("DatePW")
wshDatePW.Visible = xlSheetVisible
Set rngCurDate = wshDatePW.Range("A:A").Find(Date, , xlValues, xlWhole)
If Not rngCurDate is Nothing Then
strPWord = rngCurDate.Offset(0,1).Text
End If
wshDatePW.Visible = xlSheetVeryHidden

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[JMB]

I would probably set the veryhidden property of a worksheet and use code
similar to what I posted (using vlookup) or Find (as Ronald suggests).
Although, I would remove the following two lines from Ron's code as it is
unnecessary to unhide/rehide the worksheet (and if the code crashes or gets
interrupted - you don't want the macro to end before your password worksheet
is unhidden).

wshDatePW.Visible = xlSheetVisible
wshDatePW.Visible = xlSheetVeryHidden


If you want the passwords entirely in VBA, you could build an array w/dates
and passwords. For example:

Sub test()
Dim arrPwords(1 To 3, 1 To 2) As Variant
Dim strPWord As String

arrPwords(1, 1) = #8/20/2007#
arrPwords(2, 1) = #8/21/2007#
arrPwords(3, 1) = #8/22/2007#
arrPwords(1, 2) = 1234
arrPwords(2, 2) = 2345
arrPwords(3, 2) = 3456

strPWord = CStr(Application.VLookup(Date, arrPwords, 2, 0))

MsgBox strPWord

End Sub

Personally, I don't go through much trouble to try to password anything in
XL as it's security (for lack of a better word) is weak. VeryHidden
worksheets can be unhidden w/a simple macro, worksheet and workbook
protection can be broken easily w/a macro, VBA project passwords can be
broken w/a hex editor or software available online, password to open can also
be cracked w/software available online. I only use passwords to keep out
casual lookers or to prevent people from accidentally overwriting things. If
someone wants to break your protection, you should assume they will.

 

[Danny]

Hi Ronald,

Thank you very much for following this up on this other posting:

I copied the macro into a module and named it FindDatePW()

When I pasted it, the first two rows:

Dim wshDatePW as Excel.Worksheet, rngCurDate as Excel.Range, strPWord as
String

turned red. I ran it anyway and it said syntax error.

My other questions are:
1. Do I have to anything else or change any of my original macro below?
2. I presume that:

Col A. Col. B
8/20/07 = 1234
8/21/07 = 2345
8/22/07 = 3456

are in worksheet "DatePW"?

Thanks again.

 

[JMB]

the two lines you refer to should be one line. you have to watch the
newsgroup word wrap.

 

[Danny]

Hi JMB,

I understand about cracking "passwords" in excel. I don't think any of the
users of the template have the knowledge to do it.

Your macro below works perfectly. It gives the user the password for a
particular day.

However, how can I write a macro to change:

"If PassWord = "1234" Then" in my original macro
to
"If PassWord = "3456" Then" because today is 8/22/07

so my original macro will run?

When I'm away from the office, a user calls me up to print a few worksheets.
So,
I have to give them the "password" for the day.


Thanks to both of you for your time and efforts.

 

[JMB]

Private Sub Workbook_BeforePrint(CANCEL As Boolean)
Dim arrPwords(1 To 3, 1 To 2) As Variant
Dim strPWord As String

arrPwords(1, 1) = #8/20/2007#
arrPwords(2, 1) = #8/21/2007#
arrPwords(3, 1) = #8/22/2007#
arrPwords(1, 2) = 1234
arrPwords(2, 2) = 2345
arrPwords(3, 2) = 3456

If Date = Now() Then
CANCEL = True
Exit Sub
End If

strPWord = CStr(Application.VLookup(Date, arrPwords, 2, 0))

PassWord = InputBox("Enter Password")
If PassWord = "" Then
CANCEL = True
Exit Sub
End If

If PassWord = strPWord Then
CANCEL = False
'CountPrinting 'Limit printing even with correct date & PW, still on the
works
Range("$A1").Select
Else
MsgBox " Try again ?", vb, "Wrong password !"
CANCEL = True
Exit Sub
End If
End Sub

 

[Danny]

Perfect!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Only one macro (the "test" macro is
consolidated)!
I'm sorry that I did not make myself clear what I needed because if I did,
you would have provided this macro in your first response. Thanks a lot JMB!
Have a great day!

 

[Ronald Dodge]

The only reason why I included the 2 lines of code as JMB refered, certain
things doesn't work on hidden objects, and if I recalled correctly, Find is
one of those methods that doesn't work unless that been fixed in a later
version. I ran into that issue head on when our scheduler couldn't do
certain things cause the code wouldn't do the specific action on the hidden
object as it was setup to.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[JMB]

I know what you mean, but Find works okay for me on XL2000 (dunno about
previous versions). I know Find( ).Select won't work on a hidden sheet.

 

[JMB]

You're welcome - thanks for the feedback.

Perfect!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Only one macro (the "test" macro is
consolidated)!
I'm sorry that I did not make myself clear what I needed because if I did,
you would have provided this macro in your first response. Thanks a lot JMB!
Have a great day!

 

[Ronald Dodge]

I wouldn't expect Select method to work anyhow as it's a method geared
towards the interaction with the interface. If the interface that it's
trying to interact with is hidden, then it wouldn't work. It may work for
when only a row or column is hidden, but not so much on a hidden worksheet.
Of course, that is dependent on how the security of the worksheets is setup,
if the worksheet is password protected.

Me personally, I don't like having passwords in files like these, but I can
certainly see Danny's viewpoint. In my production reporting system on the
client side, I also have the worksheets, workbook, and VBA code password
protected on the client side with the code unprotecting then protecting on
an as needed basis, which also introduces potential issues. However, those
passwords are put in more so to keep the users from trying to fudge the
numbers than it is to keep them from doing malicious activities. If the
latter was the case, they would be escorted out and lose their employment.

If anything, this production reporting system that I created for them has in
many respects helped everyone out that's impacted by this program. Numbers
are no longer recorded by hand, thus with the data checks and calculations
automated as opposed done by hand prior, it not only knocked out a lot of
the potential for human errors, but also allowed for the data to be more
real time and much more accurate. Believe it or not, this program was built
and debugged over a 3 week period along with the operators and assistants
trained over the last 3 days of that same 3 week time period. It's been in
full use since the start of July 2001, which was only meant as an
intermediate program, and still is being used today. Operators/Assistants
use the client side, and the server side updates our main DB system, which I
got around a lot of the issues in Excel that you would expect to face. That
was using XL97, SR2, which I hated that version cause of all the technical
issues that I faced with that version. XL2K though fixed a pretty majority
of those issues that I faced, which MS did send to me as a fix to the bugs
that I faced in XL97 and they confirmed as bugs, but wasn't going to fix in
97 as they were already fixed in XL2K. With XL2K, I did face some new
issues, but nothing too major that would have prevented me from using it for
my own purpose. However, due to the compilation differences between XL2K
and XL97, if anything involved VBA and others were going to use it, I had to
use XL97 for that purpose. However, for the last 4 years after we got
upgraded to W2K environment, we been using XLXP. The 2 biggest reasons why
were:

Couldn't print in landscape mode over the network without having to put a
spool driver on the individual computer for the printer the user was
printing to (what a royal pain that was)

If the XL97 file contained VBA codes, no such file had been opened on the
user's system since the W2K Pro installation, and the user was marked as a
"Standard User" in the W2K environment, when XL97 would attempt to not only
access the registry file, but also write to it, it would come back as a VBA
permission denial error message. A "Power User" or higher would have to
open a such file one time on the system, then close it out before a
"Standard User" would be allowed to open a such file, as this was a one time
write to the registry file.
--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000