determine cell format

[kmiles]

using the worksheet_selectchange event, I need to determine if the target cell is formatted in mm/dd/yyyy format. I know I should be able to use some variation of the cell("format") function along with application.worksheetfunction but I can't seem to get this to work.

I know the value returned by the cell function is D4 for the above mentioned format.

All help is greatly appreciated !

 

[erik.van.geit]

Hello, kmiles,
try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.NumberFormat = "mm/dd/yyyy" Then MsgBox "yes"
End Sub

kind regards,
Erik

 

[Joe Was]

Sub cellFormat()

myFmat = Selection.NumberFormat

myTyp = myTyp & vbLf & myFmat

MsgBox myTyp

End Sub



To test for a defined type of format:

Sub cellFormatTest()
If Selection.NumberFormat <> "m/d/yyyy" Then MsgBox "Not: m/d/yyyy"
End Sub

 

[kmiles]

Those won't work because the cell is formatted as a date, not a number.

The formula below works in Excel, I just need to make it work in VB.

assuming 3/15/2005 is in A1, formatted as date mm/dd/yyyy

then

in b1
=cell("format",a1) will return the value D4, which I can then easily test for.

 

[erik.van.geit]

Those won't work because the cell is formatted as a date, not a number.

the format has nothing to do with the syntax .NumberFormat
what do you think this code is doing ?
ActiveCell.NumberFormat = "@"
it is formatting as a string ! (not a number)

what do you get using this code ?

Range("A1") = ActiveCell.NumberFormat

The contents of A1 is what you will have to paste in the code (between quotes)

just using some tricks can solve your problems or still no luck ? ...

best regards,
Erik

 

[kmiles]

Thanks for your help Erik.

The code below works exactly as I needed:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

cellFmt = Target.NumberFormat
If Left(cellFmt, 8) = "m/d/yyyy" Then
a = MsgBox("Cell " & Target.Address & " is formatted as m/d/yyyy", vbOKOnly)
End If

End Sub