Data entry form macro shows wrong formats

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top