VBA Format function won't work

[pirdop]

Hello all,
Any ideas why the Format function won’t work?

I collect a date via an userform and when I try to output it to a spread sheet it changes the format randomly.
I tried to use unsuccessfully the format function to correct this.

 With ws
                        .Cells(iRow, 2).Value = Format(Me.DateBox.Value, "dd/mm/yyyy")
                            .Cells(iRow, 2) = Format(.Cells(iRow, 2), "dd/mm/yyyy") 'formats Cells containing a DATE in dd/mm/yyyy format
                            .Cells(iRow, 2).HorizontalAlignment = xlLeft 'formats Cell containing a DATE

Tried both the lines. No success so far L

I want the date to be stored always in the dd/mm/yyyy format

thx

 

[pirdop]

PS. the format changes randomly to either m/d/yyyy either to d/m/yyyy

 

[VoG]

Try

With .Cells(iRow, 2)
    .Value = DateValue(Me.DateBox.Value)
    .NumberFormat = "dd/mm/yyyy"
End With

 

[pirdop]

thank you VoG,
I changed a bit your code to accommodate it to mine

With ws
                        .Cells(iRow, 2).Value = DateValue(Me.DateBox.Value)
                            .Cells(iRow, 2).NumberFormat = "dd/mm/yyyy"
                            .Cells(iRow, 2).HorizontalAlignment = xlLeft
'some more code
end with

It seems to be working now..... or at least out of 10 tries I didn't manage to reproduce the previous behavior.


Any logical explanation on why the format didn't work?