Select Case Criteria based on cell contents question

[Pinaceous]

Hello,

I'm trying to construct a Select Case that is based on the contents of a cell.

For example, in using cell A1, if a user enters in a date item versus a non-date entry into that cell it will produce:

Case; If cell A1; if it has a date entry then  MsgBox "Date".

Case; If cell A1; if it is not a date entry then  MsgBox "This is not a Date".

Can someone please help me construct a Select Case for this type of situation?

Many thanks in advance,
pinaceous

 

[JoeMo]

Within the scope of the IsDate function (which is described below the code)

Sub Pinaceous()
Select Case True
    Case IsDate([A1]): MsgBox "Date"
    Case Else: MsgBox "This is not a date"
End Select
End Sub

From VBA Help:

IsDate Function

<tbody>
</tbody>

Returns a Boolean value indicating whether an expression can be converted to a date.
Syntax
IsDate(expression)
The required expression argument is a Variant containing a date expression or string expression recognizable as a date or time.
Remarks
IsDate returns True if the expression is a date or is recognizable as a valid date; otherwise, it returns False. In Microsoft Windows, the range of valid dates is January 1, 100 A.D. through December 31, 9999 A.D.; the ranges vary among operating systems.

 

[Pinaceous]

Thank you for the code JoeMo!

Could you add to this code, whereby if the cell of A1 is blank [that is Formatted for the Date *Wednesday, March 14, 2014], it returns MsgBox "This cell is blank"?


Thanks again,
Paul

 

[JoeMo]

You are welcome. The revision below covers the blank cell addition.

Sub Pinaceous()
Select Case True
    Case IsDate([A1]): MsgBox "Date"
    Case Else
        If IsEmpty(Range("A1")) Then
            MsgBox "This cell is blank"
        Else
            MsgBox "This is not a date"
        End If
End Select
End Sub

 

[Pinaceous]

Thanks JoeMo!


That worked out really well, many thanks for helping me out!

Paul