I want to have an input box that will ask for a date when a workbook is
opened.
I want to be able to us the date that is input in code that will be run
from a macro.

The reason I want the input box upon open instead of when the macro is
run, is because the end user uses the file repeatedly for multiple
calculations. This date will be applied to all of the calculations,
and I don't want the user to have to enter the same date over and over.


I assume that I need something like a "public enddate as date" in the
ThisWorkbook object.


This however doesn't work. What do I need to do to be able to
reference the inputed date later?


TIA

Hi Papa

'top of a normal module
Public pDate As Date

Sub GetDate()
Dim minDate As Date, maxDate As Date
Dim n As Byte
Dim sTtile As String, sPrompt As String, s As String

pDate = 0
minDate = Date - 7 '7 days ago
maxDate = Date + 365 '1 year hence

sTitle = "My Title"
sPrompt = "Enter date between " & minDate & " & " & maxDate

On Error Resume Next
retry:
n = n + 1
v = InputBox(s & sPrompt, sTitle, Date)
pDate = CDate(v)

If pDate = 0 Or pDate < minDate Or dt maxDate Then
If n < 5 Then
s = v & " not valid" & vbCr
GoTo retry
Else
MsgBox "Give up"
End If
Else
Reply = MsgBox(Format(pDate, "yyyy mmm dd"), vbYesNoCancel)
If Reply = vbNo Then
n = 0
GoTo retry
ElseIf Reply = vbCancel Then
pDate = 0
End If

End If


End Sub

Sub test()

If pDate = 0 Then
GetDate
Else: MsgBox "already got date " & pDate
End If
End Sub

'Sub Auto_Open()
'GetDate
'End Sub


You could get your date from Auto_Open or Workbook_Open event, but why not
place this line in any routine that needs the date:

If pDate = 0 Then GetDate

But if you really want to get it on Open uncomment the Auto_Open sub.

Edit or remove the stuff about min/max dates and retrys (if any) and
confirmation.

Regards,
Peter T


"Papa Jonah" wrote in message
...
I want to have an input box that will ask for a date when a workbook is
opened.
I want to be able to us the date that is input in code that will be run
from a macro.

The reason I want the input box upon open instead of when the macro is
run, is because the end user uses the file repeatedly for multiple
calculations. This date will be applied to all of the calculations,
and I don't want the user to have to enter the same date over and over.


I assume that I need something like a "public enddate as date" in the
ThisWorkbook object.


This however doesn't work. What do I need to do to be able to
reference the inputed date later?


TIA