A
Axel07
My Excel 2007 table has columns containing dates in the format 2009-11-15 and
times in the format 14:16 ( [h]:mm ). I open a data entry form by clicking on
the form button in the Quick Access Toolbar. I insert the current date using
the keyboard shortcut Control-semicolon. The date appears in the expected
format.
I prefer to open the data entry form using the following macro, to which I
have assigned the keyboard shortcut Control-Shift-O:
Sub OpenDataForm()
' Keyboard Shortcut: Ctrl+Shift+O
ActiveSheet.ShowDataForm
End Sub
However, when I open the data entry form using the macro, the form does not
recognize the format setting in the Excel table, and displays dates in the
format 11/15/2009. Similarly, times appear in AM/PM format instead of my
24-hour format. When the form data is saved as a new entry, these unwanted
formats appear in the table.
(1) Why do the formats differ depending on whether the data entry form is
invoked by button-clicking or the macro?
(2) How should I rewrite the macro to ensure that it picks up format
settings from the Excel table?
Thanks.
David
times in the format 14:16 ( [h]:mm ). I open a data entry form by clicking on
the form button in the Quick Access Toolbar. I insert the current date using
the keyboard shortcut Control-semicolon. The date appears in the expected
format.
I prefer to open the data entry form using the following macro, to which I
have assigned the keyboard shortcut Control-Shift-O:
Sub OpenDataForm()
' Keyboard Shortcut: Ctrl+Shift+O
ActiveSheet.ShowDataForm
End Sub
However, when I open the data entry form using the macro, the form does not
recognize the format setting in the Excel table, and displays dates in the
format 11/15/2009. Similarly, times appear in AM/PM format instead of my
24-hour format. When the form data is saved as a new entry, these unwanted
formats appear in the table.
(1) Why do the formats differ depending on whether the data entry form is
invoked by button-clicking or the macro?
(2) How should I rewrite the macro to ensure that it picks up format
settings from the Excel table?
Thanks.
David