input box upon opening workbook

[Guest]

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

 

[Peter T]

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

 

[Guest]

Peter,
I used what you suggested as an idea. I made a routine, GetDate. However,
I put a line in the open workbook thing that told it to run GetDate. That
part works good. However, (please look past my ignorance) I wonder if I need
to employ a "property". I am not clear what those are, but . . .
Currently, I have included a variable that is either 1 or 0 depending on
whether the user choses to set a default date for all of their calculations.
This resets after the first calculation is done. I want the value to stay
the same for as long as this workbook is open.

What my workbook is used for is the user pastes data on the sheet in the
workbook. With some macros, a copy of the workbook is made and saved to a
new file, a bunch of calcs are done and charts are made. Then the user can
replace the set of data and do it all again. And of course a new file is
generated. However, I want the date variable to be consistently applied
everytime the user runs the macros with new data sets.

Do you think I need to learn something about properties for this?

Thanks for your time.

 

[Peter T]

First, before I forget, in my code example change the three uses of "Reply"
to say "ans", and declare it at the top:
Dim ans as Long

"Reply" is a keyword that shouldn't be used as a variable.

I wonder if I need to employ a "property"

If the public variable "pDate" is declared at the top of a normal module,
no. It will be visible throughout your project and permanent* while the
workbook is open. Just use or change as is.

Did you put all that code in ThisWorkbook module, which is a Class module.
If so, why? Unless you have a good reason it only complicates things (though
possibly good practice in some circumstances). You can call "GetDate" in a
normal module from the open event in ThisWorkbook.

But if you have declared "pDate" at the top of a class module, then yes, you
would need to use Property Let / Get method to change or get its value from
other modules. See Help, but what's not quite clear in help is if calling
from outside the class module you need to append the Property procedure name
with "ThisWorkbook.", assuming it's in that class module. Quick example:

'top of ThisWorkbook module
Dim clsDate As Date

Public Property Let theDate(dt As Date)
clsDate = dt
End Property

Public Property Get theDate() As Date
theDate = clsDate
End Property

' end code ThisWorkbook

'code in normal module
Sub fixDate()
Dim mydate As Date
mydate = (Date - 7) 'use the GetDate procedure to set myDate
ThisWorkbook.theDate = mydate
End Sub

Sub retrieveDate()
Dim mydate As Date
mydate = ThisWorkbook.theDate
MsgBox mydate
End Sub

But as I said, all this is unlikely to be necessary for your needs. Keep
your public variables at the top of a normal module and don't worry about
Property. I didn't quite follow what you're doing with the 0/1 variable, but
similarly declare it as Public at top of a normal module.

* Public variables normally "live" while the workbook remains open but can
be destroyed, eg save the workbook, reset the project. That's why it's a
good idea to check they are valid before using them, hence my previous
suggestion of this line in each macro that uses pDate:

If pDate = 0 Then GetDate

An alternative to using public variables is to put and retrieve values in
cells on the worksheet. You may or may not want to save these with the
workbook.

Regards,
Peter T