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!










"JMB" wrote:

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" wrote:

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" wrote:

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" wrote:

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