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

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

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

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

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

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

"Danny" wrote in message
...
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

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

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