Validate Date entered into cell-CodePudding

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

CodePudding user response：

I think that

Edate = Range("S2")

Should be

Edate = Range("S2").value

CodePudding user response：

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