Verify user input box is a Month End Date

[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

 

[Dave Peterson]

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

 

[mikeburg]

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

 

[Dave Peterson]

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

 

[mikeburg]

Thanks so very much. mikeburg