Validate Date Entry in Inputbox

How to validate a date entry in an inputbox during report generation from worksheet data based on dates. Last time we learnt how to generate a report from data in a worksheet based on dates. Now the viewers had the following queries:

  • What if the user enters no data i. e. he leaves the inputbox blank and presses enter
  • What if he enters a date in an invalid format into the input-box
  • What if I want to generate a report based on dates and a specific product

Watch the video below to see how a solution to these queries can be found quickly and easily.

Watch this video on Youtube.

Here’s the complete VBA code to validate the date input into the input-box and generate the report:

Sub reportGenerationBasedonDates()
Dim lastrow As Long, i As Long, erow As Long
‘Dim sheetdate As Date
Dim startdate As String, enddate As String

lastrow = Worksheets(“sheet1”).UsedRange.Rows.Count
ReTry:
startdate = InputBox(“Enter start date as mm-dd-yyyy”, “Enter start date”, Format(Date, “mm-dd-yyyy”))
‘MsgBox “You entered ” & startdate
If startdate = “” Then
MsgBox “You did not enter a date!”
Exit Sub
End If
If Not IsDate(startdate) Then
If MsgBox(“Invalid start date! ” & “Enter date in correct format.”, vbRetryCancel = vbRetry) Then
GoTo ReTry
Else: Exit Sub
End If

End If

enddate = InputBox(“Enter end date as mm-dd-yyyy”, “Enter end date”, Format(Date, “mm-dd-yyyy”))
‘MsgBox “You entered ” & enddate
If enddate = “” Then
MsgBox “You did not enter a date!”
Exit Sub
End If
If Not IsDate(enddate) Then
If MsgBox(“Invalid end date! ” & “Enter date in correct format.”, vbRetryCancel = vbRetry) Then
GoTo ReTry
Else: Exit Sub
End If

End If

For i = 2 To lastrow
If Cells(i, 1) >= startdate And Cells(i, 1) <= enddate And Cells(i, 2) = “i7 Intel CPU” Then
erow = Worksheets(“sheet2”).UsedRange.Rows.Count + 1
Range(Cells(i, 1), Cells(i, 5)).Copy Destination:=Sheets(“sheet2”).Cells(erow, 1)
End If
Next i

End Sub

Further reading:
Verify that the user entered a date in an InputBox

Leave a Reply

Your email address will not be published. Required fields are marked *