Getting cell format in VBA.

[HighAndWilder]

Hi All.

I have a cell with a number format of DD/MM/YYYY HH:MM.

I want to be able to ascertain this in VBA.

Using the following returns m/d/yyyy h:mm.

strFormat = range("A1").NumberFormat

I need the exact format to format text in a Userform listbox.

How can this be achieved?

Thanks

 

[Joe4]

Are you sure that you are checking the correct cell?
And, are you sure that you are on the right sheet when you run that code?

If I apply that exact format to cell A1, and then run this VBA code:

Sub Test()
    MsgBox Range("A1").NumberFormat
End Sub

it returns the following for me: dd/mm/yyyy hh:mm
which appears to be exactly what you are looking for.

 

[HighAndWilder]

Correct sheet and correct cell.

In the Format Cell Dialog Box the cell format is dd/mm/yyyy hh:mm but when I try to access it in VBA I get m/d/yyyy h:mm.

If I change the format in VBA to dd/mm/yyyy hh:mm I then get the correct format when I use the following code.

strFormat = range("A1").NumberFormat

 

[Joe4]

What does it return if your try this?

Sub Test2()
    Range("A1").NumberFormat = "dd/mm/yyyy hh:mm"
    MsgBox Range("A1").NumberFormat
End Sub

If that still does not return what you expect, try this version:

Sub Test3()
    Application.EnableEvents = False
    Range("A1").NumberFormat = "dd/mm/yyyy hh:mm"
    MsgBox Range("A1").NumberFormat
    Application.EnableEvents = True
End Sub