Verify user input box is a Month End Date

  • Thread starter Thread starter mikeburg
  • Start date Start date
M

mikeburg

I am using the following VBA code to prompt for the Month End Date th
user is working on.

Dim monthenddate As String
Dim monthendname As String
monthenddate = Application.InputBox("Enter month end date ex-03-31-06
")
monthendname = Replace(monthenddate, "-", "")
If monthendname = "" Then GoTo done
If monthendname = False Then GoTo done

What would be the VBA code to verify the date the user enters is
month end date? In other words, they are entering 7-31-06, not 7-30-0
or 7-18-06.

Thanks for your help. mikebur
 
Instead of verifying it, why not just adjust the value?

Option Explicit
Sub testme()

Dim MonthEndDate As Date
'ask for a number with type:=1
MonthEndDate = Application.InputBox("Enter month end date ex-03-31-06:", _
Type:=1)

'some minor validation
If Year(MonthEndDate) < 2000 _
Or Year(MonthEndDate) > 2020 Then
MsgBox "Please try later"
Exit Sub
End If

'the zeroeth day of the next month is the last
'day of the current month
MonthEndDate = DateSerial(Year(MonthEndDate), Month(MonthEndDate) + 1, 0)

'just to show that it worked
MsgBox MonthEndDate
End Sub
 
Great idea! I will use this in the VBA code.

However, the Month End Date still needs to be verified to give the use
a chance to correct the input when the wrong month is entered. Fo
example, when 11-31-06 is entered for 1-31-06. For example:

"11-31-06 is not a Month End Date!
Did you mean 11-30-06?
If so, press enter, otherwise, enter the correct Month End Date:"

Any VBA code to do this would be greatly appreciated. Thanks, mikebur
 
I'm not sure what should happen if the user hits the cancel key, but this worked
ok for me:

Option Explicit
Sub testme()

Dim MonthEndDate As Date
Dim resp As Long
Dim DateIsOk As Boolean

DateIsOk = False
Do
MonthEndDate = Application.InputBox _
("Enter month end date ex-03-31-06:", Type:=1)

If Year(MonthEndDate) < 2000 _
Or Year(MonthEndDate) > 2020 Then
MsgBox "Please try later"
Exit Sub
End If

MonthEndDate = DateSerial(Year(MonthEndDate), _
Month(MonthEndDate) + 1, 0)

resp = MsgBox(Prompt:="Is this the date you want to use?" & vbLf _
& Format(MonthEndDate, "mmmm dd, yyyy"), _
Buttons:=vbYesNo)

If resp = vbYes Then
DateIsOk = True
Exit Do
End If
Loop

If DateIsOk Then
MsgBox MonthEndDate
End If
End Sub

And excel wouldn't even let me enter 11/31/2006. It recognized that that wasn't
a real date!

Another option...

You could build a small user form and use calendar control so that the user
could point at a date.

Ron de Bruin has some tips/links at:
http://www.rondebruin.nl/calendar.htm
 
Back
Top