VBA - evaluate if a cell is formatted as a date (of *any* date type)

[wintersm]

I have a Task List workbook that launches a calendar app (Userform Control called MonthView) whenever a "date cell" is double-clicked. "Date Cells" are identified by their NumberFormat (mm/dd/yyyy). So the function tests the cell NumberFormat, and if it is "mm/dd/yyyy" then it loads the calendar so the user can select a new date. I would like to expand the evaluation to identify ANY date formats, not just my pre-set "mm/dd/yyyy". The cell might be blank to start, so I cannot test if the cell value is a date, and I'd rather not use the header as an indicator (ex. "If header contains 'date' then load calendar").

Is there a way to test that the target cell is formatted as a date without specifying the exact date type?

Thanks in advance for any ideas.

-Mike

 

[ZVI]

Use this in BeforeDoubleClick event code:

  If IsDate(Target(1).Value) Then
    MonthView.Show
    Cancel = True
  End If

Regards

 

[wintersm]

Use this in BeforeDoubleClick event code:

  If IsDate(Target(1).Value) Then
    MonthView.Show
    Cancel = True
  End If

Regards

Thanks for taking the time to respond to my question. I'll give it a shot and report back. The cell value may be blank in some cases. It will be interesting to see if this works in that case.

-Mike.

 

[ZVI]

... The cell value may be blank in some cases. It will be interesting to see if this works in that case.

-Mike.

Hi Mike,

To show form on empty cell too, the code might be as follows:

  If IsDate(Target(1).Value) Or IsEmpty(Target(1).Value) Then
    MonthView.Show
    Cancel = True
  End If

Target(1) is the same as Target.Cells(1) and used for the case of double clicking on joined cells.

Regards,
Vlad

 

[Rick Rothstein]

To show form on empty cell too, the code might be as follows:

If IsDate(Target(1).Value) Or IsEmpty(Target(1).Value) Then

I don't like the IsDate function as it will return True for odd values (for example, try 1.2 in a cell formatted as Text or 5/6-78 in a cell formatted as General or Text). Now, this probably won't be a problem for the OP as he seems to have defined his situation, but we cannot be sure.

Going back to the question as asked...

"Is there a way to test that the target cell is formatted
as a date without specifying the exact date type?"

here is a way...

If Evaluate("CELL(""format""," & Target(1).Address & ")") Like "D[1-5]" Then

 

[ZVI]

Hi Rick,
Good point - thank you!
This can be used for data value and empty value:

  Dim vt As VbVarType
  vt = VarType(Target(1).Value)
  If vt = vbDate Or vt = vbEmpty Then
    MonthView.Show
    Cancel = True
  End If

Regards,
Vlad

 

[ZVI]

...Going back to the question as asked...

"Is there a way to test that the target cell is formatted
as a date without specifying the exact date type?"

here is a way...

If Evaluate("CELL(""format""," & Target(1).Address & ")") Like "D[1-5]" Then

On my testing it does not work correctly with some date formats of cells.
Seems this works more correctly:

  If VarType(Target(1).Value) = vbDate Or Val(Format(0, Target(1).NumberFormat)) <> 0 Then
    MonthView.Show
    Cancel = True
  End If

Looks like it is also not strict, but may be acceptable enough for major cases

 

[ZVI]

And usually if cell has formula then calendar do not appear, or at least should not change that cell's value to prevent formula disappearing.
If Target(1).HasFormula Then Exit Sub