Validate Date entered into cell

Question

I have a workbook with a field for a user to enter a date. In testing, I entered 11/31/21 which is not a valid date. I thought I would test for valid date entry and can't seem to make it work. I am trying to use IsDate to check and must being doing something wrong. I tried setting my cell value as both date and string with no luck.

Public Sub Worksheet_Change(ByVal Target As Range)
    Dim PCell As Range
    Dim Edate As Date
   
    
    Set PCell = Range("S2")
        Edate = Range("S2") 
    
If Not Application.Intersect(PCell, Range(Target.Address)) _
           Is Nothing Then
    If IsDate(Edate) = 1 Then
    ActiveWorkbook.Connections("Query - SelectedDataByDate").Refresh
Else
MsgBox ("You have entered an invalide date.")
End If

End If

End Sub

November does have 30 days... So why is it not a valid date? — Solar Mike, Feb 10 '22 at 20:48
First `Dim EDate as Variant` and also True in VBA is possibly not 1: Just use `If IsDate(EDate) Then …` .See https://stackoverflow.com/questions/3621037/casting-a-boolean-to-an-integer-returns-1-for-true — DS_London, Feb 10 '22 at 23:11

score 0 · Answer 1 · answered Feb 10 '22 at 21:00

0

I think that

Edate = Range("S2")

Should be

Edate = Range("S2").value

answered Feb 10 '22 at 21:00

1

Being explicit about the `.Value` is a good idea, but it won't change anything. – BigBen Feb 10 '22 at 21:04

score 0 · Answer 2 · answered Feb 11 '22 at 03:23

Thanks for your help. @DS_London, it was the case that VBA did not use 1 but rather True and False.

This code ran as expected.

Public Sub Worksheet_Change(ByVal Target As Range)
    Dim PCell As Range
    Dim Edate As Variant
       
    
    Set PCell = Range("S2")
        Edate = Range("S2")
        
   
    
If Not Application.Intersect(PCell, Range(Target.Address)) _
           Is Nothing Then
    If IsDate(Edate) = True Then
    ActiveWorkbook.Connections("Query - SelectedDataByDate").Refresh
Else
MsgBox ("You have entered an invalide date.")
End If

End If

End Sub

Glad it's working: you can just use `If IsDate(Edate) then ...`. `IsDate()` already returns a Boolean so you don't need the `=True` test. — DS_London, Feb 11 '22 at 16:34

Validate Date entered into cell

2 Answers2