Hey all,

I have this code (which I got from this forum... thank you!) in a worksheet but I need a format code as well.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A" & Target.Row).Value = Date & Time
Application.EnableEvents = True
End Sub


Excel is returning this format:

11/21/20089:10:55 AM (This is correct except it's formatted wrong)

I've tried formating the cells to: m/d/yy h:mm but I still get the above format. The only thing I can conclude is that I must have a format command in the code as well.

Please help!

Hello

Try this:

Range("A" & Target.Row).Value = Format(Date & " " & Time, "m/d/yy h:mm")

Regards,
Per

"Keyrookie" skrev i meddelelsen
...

Hey all,

I have this code (which I got from this forum... thank you!) in a
worksheet but I need a format code as well.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A" & Target.Row).Value = Date & Time
Application.EnableEvents = True
End Sub


Excel is returning this format:

11/21/20089:10:55 AM (This is correct except it's formatted wrong)

I've tried formating the cells to: m/d/yy h:mm but I still get the
above format. The only thing I can conclude is that I must have a
format command in the code as well.

Please help!




--
Keyrookie

You are assigning...

Date & Time

in the 2nd line of the subroutine. This butts up the date next to the time
with nothing between them... Excel won't see that as a date/time
combination. You need a space between them. Try assigning this instead...

Date & " " & Time

I think you will then be able to format the cell any way that you want.

--
Rick (MVP - Excel)


"Keyrookie" wrote in message
...

Hey all,

I have this code (which I got from this forum... thank you!) in a
worksheet but I need a format code as well.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A" & Target.Row).Value = Date & Time
Application.EnableEvents = True
End Sub


Excel is returning this format:

11/21/20089:10:55 AM (This is correct except it's formatted wrong)

I've tried formating the cells to: m/d/yy h:mm but I still get the
above format. The only thing I can conclude is that I must have a
format command in the code as well.

Please help!




--
Keyrookie

Thanks Per, that did the trick! Also, for anyone else looking for this result, cells must be custom formatted to "m/d/yy h:mm AM/PM" otherwise Execl will return military time.

Thanks again for the quick response,

K