This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
XL, for good reason, requires times to be entered using colon (:) delimiters. It would be problematic for a general parser to determine if the user intended to enter a time otherwise. MVP Chip Pearson has published a method for entering times without any delimiters, within some fairly tight constraints.
This method makes entering lists of times easier by allowing the user to more easily use the numeric keypad. It inserts an autocorrect entry that changes the decimal point to a colon. So entering
in a cell is automatically converted to
and you can enter
by typing
Naturally the display of the time (including AM/PM) will depend on the cell's Number Format. For the greatest efficiency, PM times should be entered in 24-hour format (e.g., 9:15 PM as 21.15).
You can download the JEM_TimeEntry.xla add-in to see how the method works (the code is open). As written, it uses a button on a toolbar to insert and remove the autocorrect, but there's no reason that the macros can't be modified to use keyboard shortcuts or event macros to toggle them. If you'd rather skip the following explanation it's not really necessary in order to run the add-in.
The first order of business is to create a toolbar with a button. I prefer to create my toolbars in code so that I don't have to rely on the user to keep things the way I want them. The critical part for this application is that the Parameter property be set to ".", or the default decimal point. See the toolbar creation code.
The autocorrect toggle is performed by checking the toolbar button's Parameter property to determine whether the entry has been made or not. If not, we'll insert it. If so, we'll remove it. When the toolbar button is clicked, the macro in the OnAction parameter is called. XL also sets the Application property ActiveControl to the toolbar button to indicate which control called the macro. If the ActiveControl's parameter is ".", then the control was in its default state (autocorrect removed) and we should create the autocorrect entry. We also change the Parameter property on the toolbar button to indicate that the autocorrect is active. To give the user some feedback, we set the button's State property to indicate that it's "pressed", and change the Tooltip.
Public Sub JEM__TE__SetAutoCorrect()
With CommandBars.ActionControl
If .Parameter = "." Then
.Parameter = ":"
.TooltipText = "Remove Time Entry autocorrect"
.State = msoButtonDown
Application.AutoCorrect.AddReplacement _
What:=".", _
Replacement:=":"
If instead the ActiveControl's Parameter property is ":", the autocorrect has been inserted and we should remove it. We also reset the Parameter property to the default ".". The button's State property is reset (NOTE: For at least one version of XL, the button appearance doesn't change until a mouseover of the cursor). Note that, to avoid a run-time error, we need to trap the removal in case the user removed it manually or another routine removed it.
Else
.Parameter = "."
.State = msoButtonUp
.TooltipText = "Create Time Entry autocorrect"
On Error Resume Next
Application.AutoCorrect.DeleteReplacement _
What:="."
On Error GoTo 0
End If
End With
End Sub
Obviously, the toobar button's Parameter property can't be used when the macro is called from some other source than the button. One solution is to have a static boolean variable that simply toggles between inserting and deleting the autocorrect entry.
Static bToggle As Boolean
bToggle = Not bToggle
If bToggle Then
'insert autocorrect
Else
'remove autocorrect
End If
Another solution might be split the setting and deleting routines, and to use an event macro to call the desired routine. For instance, using the Worksheet_SelectionChange event to set the autocorrect when the input range is entered, and to delete it when the range is exited.
The JEM_TimeEntry.xla add-in adds a couple of features to the description above. To prevent inserting the autocorrect while working in one workbook and then switching to another workbook for which the autocorrect isn't desired, an Application_WorkbookDeactivate event macro was added (see code). When a workbook is switched or closed, the toobar's Parameter property is reset and the SetAutoCorrect routine is run to remove the autocorrect. A Workbook_Close event macro was also added to remove the toobar when the add-in is unloaded.
Download the JEM_TimeEntry.xla add-in.
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
Chip Pearson's "Quick Date/Time Entry"
Download the JEM_TimeEntry.xla add-in
Unfamiliar with macros? See David McRitchie's "Getting Started with Macros and User Defined Functions"