Need to substitute text for faster data entry

[Marcia]

Hi! I need to add large strings of time entries (hours and minutes)
in a columns that are formatted as "HH:MM".

For example:
1:30
2:15
8:25
-----
12:10

I can type MUCH faster, however, if I could use the decimal point (.)
instead of the colon ) to enter the data. Is there some way to
automatically substitute the decimal point for the colon as I type?
I thought about using AutoCorrect, but there is no way to confine it
to this particular workbook and an entry like this would create major
havoc in all my other files.

Any help would be greatly appreciated!

Jessi

 

[Cesar Zapata]

Marcia,


here is a code you can use for faster data entry.

Private Sub Worksheet_Change(ByVal Target As Range)
UserInput = Target.Value
If UserInput > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End Sub



this will make it so when you type 800 it will change it to 8:00 AM
for afternoons you need to use military time like 1400 for 2:00 PM

Regards,

Cesar Zapata

 

[Frank Kabel]

Hi Marcia
you may have a look at
http://www.cpearson.com/excel/DateTimeEntry.htm

for an event procedure which will allow you this 'fast-entry'

 

[JE McGimpsey]

You could toggle Autocorrect on and off:

Public Sub ToggleDotTime()
Dim strmsg As String
strmsg = "Decimal Point is NORMAL"
With Application.AutoCorrect
On Error Resume Next
.DeleteReplacement (".")
If Err Then
.AddReplacement ".", ":"
strmsg = Application.Substitute(strmsg, _
"is NORMAL", "is substituted by "":""")
End If
On Error GoTo 0
ShowStatusBar strmsg
End With
End Sub


Public Sub ShowStatusBar(Optional ByVal strmsg As String)
Const NUMSECS As Long = 3
If strmsg = "" Then
Application.StatusBar = False
Else
Application.StatusBar = strmsg
Application.OnTime Now + TimeSerial(0, 0, NUMSECS), _
"ShowStatusBar"
End If
End Sub

the ShowStatusBar routine makes it easier to see what mode your
autocorrect is in.

Private Sub Worksheet_Deactivate()
On Error Resume Next
Application.AutoCorrect.DeleteReplacement (".")
On Error GoTo 0
End Sub


You could attach ToggleDotTime to a toolbar button, a keyboard shortcut,
or you could call it from a Worksheet_ChangeSelection() event.

For safety, you should probably also have this event macro in your
worksheet code module so that your Autocorrect doesn't affect other
workbooks or applications:

 

[JE McGimpsey]

That doesn't seem to work for me.

When I enter 800, the cell displays

800:00:00

and if I enter, say, 131, then I see

131:31

 

[JE McGimpsey]

take a look at

http://cpearson.com/excel/DateTimeEntry.htm

for another way to enter times quickly.

 

[Guest]

Here's one way. Use Format/Style for the cells in which you want to make this substitution and give it a special name, like time, and give it a number format of text. Then right-click the sheet tab, select View Code, and enter this
Private Sub Worksheet_Change(ByVal Target As Range
If Target.Style = "time" The
Target.Style = "Normal
Target.Value = Application.Substitute(Target.Value, ".", ":"
End I
End Su

This will have a 1-time application (per cell) since it changes the style back to Normal, so if you make a mistake, you'll either have to re-enter it using the colon or reapply the "time" style. It can't be left as text because you won't be able to do any kind of adding of the time when you're done if you do
HTH

 

[Marcia]

Hi, everyone. Thanks for your help.

I liked Mr. McGimpsey's idea of toggling the AutoCorrect on and off,
so I initially incorporated this solution into the Workbook_Open and
Workbook_BeforeClose events, and it works great. **BUT** then it
occurred to me... I will be using my "Time Calculator" file at the
same time that I will be using my company's Timesheet file, and I'm
worried about this AutoCorrect replacement entry being active while
I'm also in the company-wide Timesheet file. I really don't want to
accidentally screw up someone's timesheet... ha!

Is there a way to limit the AutoCorrect to just my "Time Calculator"
file?


I also tried Mr. Umlas' suggestion to format the cells using the
"Time" style (because I liked the idea of limiting this substitution
process to a select group of cells), but I must have done something
wrong because it converts my hours/minutes to something else. For
example, when I type the number 1.15 (which represents 1 hour and 15
minutes), I want it to display "1:15". This does not do that,
however... it converts "1.15" to "3:36". Did I do something wrong?
I am using Excel 2002.

Thanks!
Jessi

 

[Peo Sjoblom]

If you use the workbook activate and deactivate it won't affect other
workbooks

 

[Marcia]

Thanks for your tip. I was not able to make this work, though. I am
very inexperienced at Excel programming, so I probably did something
wrong.

I doubleclicked "This Workbook" underneath the filename in the VBA
Project Window, and typed the following:

Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
Application.AutoCorrect.DeleteReplacement "."
End Sub

But when I change between two open workbooks, the second workbook
still displays the AutoCorrect entry. Did I do it wrong?

Thanks,
jessi

 

[JE McGimpsey]

App_WorkbookDeactivate is used to define your own class of event
handlers.


To limit the effect to one workbook, try putting this in the
ThisWorkbook module:

Private Sub Workbook_Deactivate()
Application.AutoCorrect.DeleteReplacement "."
End Sub

You might also take a look at the add-in (based on this thread) that
I've posted at

http://www.mcgimpsey.com/excel/easiertimeentry.html

which makes the substitution available for any workbook. If you download
the add-in and look at the code, you can see how the application event
handler works.

 

[Marcia]

Thanks, guys! You've solved all my problems.

Jessi