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 <strong>exact text</strong> that I enter because Excel appears to be treating 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.<hr>
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<hr>
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