CODEWITHSUNDEEP
vbaexcel
GunnRos86
GunnRos86

Reputation: 33

Allowing date entries later or equal to current date

A column in a sheet that I am working for, accepts date values. What I would like to do is to permit as valid user entries only dates that are equal or come after the current date. So, in a intent of mine a came up with the following: 

Dim StageDate As date
If Target.Column = 11 Then 
  StageDate = InputBox("Enter a Valid Date")
  If StageDate <= Date Then Target.value = StageDate
  Else: MsgBox("Please enter a valid date")
  End If 
End If

This doesn't work very nice. Could I ask for your proposals? Thank so much!

Upvotes: 0

Views: 50

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

May be a bit much. I always like to test that a proper date has been entered first. 1<date will return True as 1 is 01/01/1900 (or is it 31/12/1899)

Public Sub Test()

    Dim dateRange As Range

    Set dateRange = ThisWorkbook.Worksheets("Sheet1").Range("A2")

    If IsDate(dateRange) Then
        If dateRange < Date Then
            MsgBox "Invalid date", vbInformation + vbOKOnly
            dateRange = Null
        End If
    Else
        dateRange = Null
    End If

End Sub

    'Check that the value entered is a date.
    'Returns TRUE/FALSE.
    'http://regexlib.com/DisplayPatterns.aspx?cattabindex=4&categoryId=5

    'Description:
    'DD.MM.YY or DD.MM.YYYY separator could be on choice '.' '/' or '-' leap years compatible, 00 is treated as year 2000.
    'Matches
    '   29.2.04 | 29/02-2004 | 3.4.05
    'Non -Matches
    '   29.2.03 | 2902.2004 | 12.31.1975
    'Author: Dany Lauener
    Public Function IsDate(ADate As Range) As Boolean

        Dim RegX As Object
        Set RegX = CreateObject("VBScript.RegExp")

        RegX.Pattern = "^(((0?[1-9]|[12]\d|3[01])[\.\-\/](0?[13578]|1[02])" & _
                       "[\.\-\/]((1[6-9]|[2-9]\d)?\d{2}))|((0?[1-9]|[12]\d|30)" & _
                       "[\.\-\/](0?[13456789]|1[012])[\.\-\/]((1[6-9]|[2-9]\d)?\d{2}))" & _
                       "|((0?[1-9]|1\d|2[0-8])[\.\-\/]0?2[\.\-\/]((1[6-9]|[2-9]\d)?\d{2}))|" & _
                       "(29[\.\-\/]0?2[\.\-\/]((1[6-9]|[2-9]\d)?(0[48]|[2468][048]|[13579][26])|" & _
                       "((16|[2468][048]|[3579][26])00)|00)))$"
        IsDate = RegX.Test(ADate)

    End Function

You could shorten the `IsDate` function to something like:

    Public Function IsDate(ADate As Range) As Boolean

        Dim tmpDate As Date

        On Error Resume Next
            tmpDate = DateValue(ADate)
            IsDate = (Err.Number = 0)
        On Error GoTo 0

    End Function

Upvotes: 1

B.G.
B.G.

Reputation: 6026

What you are looking for is DateValue();

https://support.office.com/en-us/article/DATEVALUE-function-df8b07d4-7761-4a93-bc33-b7471bbff252

With this you can compare Dates:

DateValue(TextBoxStartDate.Text) < DateValue(TextBoxEndDate.Text)

Upvotes: 0

Related Questions