I encountered an issue while coding on VBA the following validation

When user enters a date vba will calculate the date difference with today's date :

1. if it is greater that 90 days, then Inactive property equals yes.
2. if is it less than 90 days, then inactive property equals no
3. if it blanks, then property check equals "Yes"
4. if it is any string, then provide message to the user, enter the correct date format

My issue is with use case 3 and 4

both cells are defaulted to value 12:00:00 AM

if I ask is cell date both are date, even though one is blank and the other one is a random text/string

Assume The user will hit enter without Date just to trigger the New Property Check to "yes"

Does anyone know how to differentiate those cells?

Thank you for your help

My code below

dateValue = Worksheets("Date").Cells(thisRowDate, 3).Value

If Not IsDate(dateValue) Then
        MsgBox "Please enter the correct Format"
        Application.enableEvents = True
        Exit Sub
    End If

CodePudding user response：

Example of the Select:

Select Case True
    Case IsBlank(Target.Value) 'if it blanks, then property check equals "Yes"
        foo = bazz
    Case Not IsNumber (Target.Value) 'if it is any string, then provide message to the user, enter the correct date format
        foo = blur
    Case Target.Value > 90 'if it is greater that 90 days, then Inactive property equals yes.
        foo = bar
    Case Tagret.Value < 90 'if is it less than 90 days, then inactive property equals no
        foo = bizz
End Select

Notice that I changed the order of your request, as once a single criteria is met, it exits the Select.

You check if blank first, then if it's a number, then move onto assessing the number.