Excel VBA - Detecting a cell's format

[CGSB]

Is there a way to call a specific procedure when a cell formatted as date is clicked?

 

[combo]

Try 'NumberFormat' property

combo

 

[CGSB]

Hi, thanks for the tip. I'm tried using the format number property but it didn't work.

I made a mistake in my previous post. The cell is not formatted as a date, but rather its Category is set to Date in the Format Cells option. Now I need a way to open a form which contains a calendar whenever someone clicks on one of those cells.

 

[Gavona]

Have a look at the BeforeRightClick event in help then post what you have tried and what you are trying to do in Forum707

Gavin

 

[combo]

The format does not cause any validation, it only describes how the cell contents is displayed. When the cell is being formatted, its category and further details, if exist, are set. So you can either test 'NumberFormat' string to be valid date format or test the contents for valid data (positive response for proper data string too). The latter:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox IsDate(Target)
End Sub

More secure would be combination of both methods.



combo