How to get the exact text a user entered into a cell in excel

I should start by saying that I know no VBA

I have to enter a lot of time values in excel. Because it really slows me down entering a colon whilst typing, I thought I would write a macro that would allow me to enter a string like "18.10" and convert it to "18:10". (Then I could just use the numpad and enter the times quickly).

I have cobbled together the function to convert any given (delimited) string into a time - that was easy. But now I am having trouble with my event handler because I cannot get at the exact text that I enter because Excel appears to be trea开发者_运维问答ting it as a number and trimming trailing 0s.

E.g. 18.10 gets converted to 18.1 - which gets converted to 18:01 (and not 18:10 as I want it to).

Here is my change handling code (copied from the interweb)

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

' This code copied from Chip Pearson
'http://www.cpearson.com/excel/DateTimeEntry.htm
If Application.Intersect(Target, Range("C6:F1000")) Is Nothing Then
    Exit Sub
End If
If Target.Cells.Count > 1 Then
    Exit Sub
End If
If Target.Value = "" Then
    Exit Sub
End If


Application.EnableEvents = False
With Target
    TimeStr = .Text  ' What should I use here????
    If .HasFormula = False Then
        .Value = ConvertToTime(TimeStr)
    End If
End With
Application.EnableEvents = True
Exit Sub

EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub

As you can see I am currently using Text - I have looked at all the properties on Target in the debugger and could not find any that looked like it was the raw entered text. Is there a way to get at the value?

Any help greatly appreciated.

Thank you Issun and Craig. Issun is correct - I am making it overly complicated (w.r.t. to conversion to time). Here is what I have come up with and works rather well.

' This requires the format of the cells to ce "0.00" to prevent trailing 0s being trimmed
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler:

Dim TimeStr As String
Dim TimeVal
' I am only entering times in this range
If Application.Intersect(Target, Range("C6:F1000")) Is Nothing Then
    Exit Sub
End If
If Target.Cells.Count > 1 Then
    Exit Sub
End If
If Target.Value = "" Then
    Exit Sub
End If

TimeStr = Replace(Target.Text, ".", ":")
TimeVal = TimeValue(TimeStr)
Application.EnableEvents = False
Target.Value = TimeVal
Target.NumberFormat = "h:mm"
Application.EnableEvents = True

Exit Sub
ErrHandler:
Exit Sub
End Sub

Sub SetTimeFormat()
    Selection.NumberFormat = "h:mm"
End Sub

Sub SetNumFormat()
    Selection.NumberFormat = "0.00"
End Sub

As mentioned in the comment, I found changing the cell format to "0.00" works well. Times entered as "18.1" get converted to "18:10" (instead of 18:01) but actually that is ok by me.

The last 2 macros I assign to a couple of hot keys so that if I do make a mistake, I can quickly convert the cell's format back to numeric and end the value again.

If you change the format of the cell to text (Format Cells -> Text), this will keep the 18.10 and allow you to parse the value easier.

Edit: When you run your macro add a line similar to:

Selection.NumberFormat = "h:mm AM/PM"

This should then reformat the cells a "time" format.

If all you want to do is change 18.10 to 18:10 then click the upper left corner of the sheet to select all cells, and change format to text. Then type away and do a search & replace for "." and ":" when you are done.

If you want, here's a fun way to auto format what you enter as time as you type it. In the VBA editor double click Sheet1 or whatever sheet you are working on and then enter this code:

Private Sub Worksheet_Change(ByVal Target As Range)

If InStr(Target.Value, ".") <> 0 Then
    Application.EnableEvents = False
    Target.Value = Replace(Target.Value, ".", ":")
    Target.NumberFormat = "h:mm AM/PM"
    Application.EnableEvents = True
End If

End Sub

What this does is first check if there is a "." in the cell you just changed or typed in. If there is, it turns off events (so the change it's about to do doesn't trigger another change event), then it replaces the "." with ":". It then converts it to the time format.

Here's an example of what happens. One note: For xx:30 you need to type an extra "." so that Excel doesn't get confused.

1.15 will become 1:15 AM

13.15 will become 1:15 PM

16:30. will become 4:30 PM