inpux box date validation

Question

I have the code below and if the user adds a date it works fine but if he adds a number, e.g. 1 or 66, instead of the date the code still runs and deletes all the columns. I can't seem to fix this.

Sub deleteCols()
    Dim early As Date
    Dim late As Date
    Dim lc As Long
    Dim i As Long
    Application.ScreenUpdating = False
On Error GoTo Errorhandler
    early = CDate(Application.InputBox(Prompt:="Please enter start date:", Type:=2))
    late = CDate(Application.InputBox(Prompt:="Please enter end date:", Type:=2))
    
    If early = 0 Then Exit Sub
    If late = 0 Then Exit Sub
     
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = lc To 8 Step -1
        If Cells(1, i) < early Then
            Cells(1, i).EntireColumn.delete
        ElseIf Cells(1, i) > late Then
            Cells(1, i).EntireColumn.delete
            Else
        End If
    Next i

Application.ScreenUpdating = True

Exit Sub

Errorhandler:
MsgBox "You need to insert a date dd/mm/yyyy"
Resume
End Sub

You can check if the inputs are reasonable before continuing. Like `If early < Date - 3650 Or early > Date + 3650 Then Exit Sub` (and do the same with `late`). This would check if the input date is within +-10 years from today. You should also do a sanity check on the inputs like `If late < early Then Exit Sub` — Toddleson, Feb 18 '22 at 16:01

HackSlash · Accepted Answer · 2022-02-18T16:31:44.603

An InputBox is not the best way to collect date input from a user. Use a date picker control. There are many posts that talk about how to add one to a custom form, like this: Formatting MM/DD/YYYY dates in textbox in VBA

You can also pull dates from a date formatted cell in the worksheet.

After you have received the date input from the user you still have to do some sanity checking to ensure a valid date was entered. That totally depends upon your requirements. Maybe the user should only be able to select times in the past, or only times in the future, or only times within a range. At the very least you should be checking for null or 0.

We can't write this for you. Only you know your requirements.

Note that 1 and 66 are valid dates because a date is the number of days since the beginning of time. In the case of VBA:

? CDate(1)
12/31/1899 

? CDate(66)
3/6/1900

I thought the beginning of time was 1900 but it looks like it starts on midnight of the last day in 1899.

Storax · Answer 2 · 2022-02-19T07:40:01.077

You could use the following function to make checks

Option Explicit

Enum enumCmp
    le
    ge
End Enum

Function checkDate(inpDt As Date, checkDt As Date, cp As enumCmp) As Boolean

    On Error GoTo EH
        
        If cp = enumCmp.le Then
            checkDate = inpDt <= checkDt
        ElseIf cp = enumCmp.ge Then
            checkDate = inpDt >= checkDt
        End If
        
    Exit Function
    
EH:
   ' just use the default

End Function

A test could look like that

Sub TestIt()
    
    Const EARLIEST = #1/1/2000#
    Dim early As Date
    Dim late As Date
    
    On Error GoTo EH
    
    early = CDate(Application.InputBox(Prompt:="Please enter start date:", Type:=2))
    late = CDate(Application.InputBox(Prompt:="Please enter end date:", Type:=2))
    
    If checkDate(early, EARLIEST, ge) _
        And checkDate(late, EARLIEST, ge) _
        And checkDate(early, late, le) _
        Then
        MsgBox "Okay"
        ' your code here
    Else
        MsgBox "Not okay"
        ' more code
    End If
    Exit Sub
EH:
    MsgBox "No date entered"
    
End Sub

inpux box date validation

2 Answers2