InputBox requesting dates between two predefined dates

G

Guest

I'm trying to figure out how to display this programmatically for the user.
I want the user to enter a date between the following two dates:

Date 1 is defined as SummaryMin
Date 2 is defined named variable CPI_SPI_PITD_LastMo

I want them to enter the date they want to use (or select one of the two).
How would y'all approach this.

Thanks in advance,
Barb Reinhardt
 
G

Guest

Barb,
Perhaps something along these lines. For this example, I set
SummaryMin as the default date.

Sub InForm()

Dim SummaryMin As Date, CPI_SPI_PITD_LastMo As Date, GetDate as Date

SummaryMin = "01/01/2006"
CPI_SPI_PITD_LastMo = "31/01/2006"

Do
getDate = DateValue(Application.InputBox("Enter Date between " & SummaryMin
& " and " & CPI_SPI_PITD_LastMo, "Title date ", SummaryMin, Type:=2))
If getDate = "False" Then Exit Sub ' Cancel
If Not Application.And(getDate >= SummaryMin, getDate <=
CPI_SPI_PITD_LastMo) Then
MsgBox "Invalid date. Please re-enter"
End If
Loop Until Application.And(getDate >= SummaryMin, getDate <=
CPI_SPI_PITD_LastMo)
End Sub
 
J

JE McGimpsey

One way:

(Your post is a bit confusing - do you want to "enter a date between the
following two dates", or "select one of the two"? I assumed the former:

Const SummaryMin = #1/1/2006#
Const CPI_SPI_PITD_LastMo = #12/31/2006#
Dim vResult As Variant
Dim dtFirst As Date
Dim dtLast As Date
dtFirst = SummaryMin
dtLast = CPI_SPI_PITD_LastMo
Do
vResult = Application.InputBox( _
Prompt:="Enter a date between " & dtFirst & _
" and " & dtLast & ":", _
Title:="Enter Date", _
Default:=dtFirst, _
Type:=1)
If vResult = False Then Exit Sub 'user cancelled
vResult = vResult - ActiveWorkbook.Date1904 * 1462
Loop Until (vResult >= dtFirst) And (vResult <= dtLast)
'Rest of macro
MsgBox "Valid date!"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top