29009

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 <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.

Answer1:

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.

Answer2:

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

Recommend

  • WPF Adorner Newbee
  • How I could Import/Implement all the native methods of an static Class into a custom Class?
  • Scraping a table using BeautifulSoup
  • Java Netty Unknown SSL content type error
  • To sort a class array in vb.net
  • 404 Error from Web Forms When Posting to Web API Controller
  • Animate svg path
  • Compare two columns if another two columns are matching in excel macro
  • How to load an internal class in end-user compiled code ? (advanced)
  • Exporting data from Excel to Outlook
  • Scala FlatMap provides wrong results
  • Position Userform differently for each ActiveCell Clicked
  • Xcache var_size error
  • How to intersect multiple polygons?
  • Intersect on ArrayList with objects groovy
  • ASP.NET Identity executes database query on every page request when logged in
  • Why does NotifyIcon not set SynchronizationContext?
  • VB.NET 2012 Property Set on Property Get
  • cell spacing in div table
  • Ubuntu and bcrypt
  • Add dynamic data to line chart from mysql database with highcharts
  • Implementing “partial void” in VB
  • Using Sax parsing to edit and write XML in VB6
  • Entity Framework Code First TPC Inheritance Self-Referencing Child Class
  • Using JRuby with Rails 3.2
  • Code in Job's Script Block after Start-Process Does not Execute
  • Excel's Macro-Recorder usage
  • Django: Count of Group Elements
  • C# - Serializing and deserializing static member
  • Excel - Autoshape get it's name from cell (value)
  • Obtain ObjectIdHex value from mgo query
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • vba code to select only visible cells in specific column except heading
  • How to include full .NET prerequisite for Wix Burn installer
  • Is it possible to post an object from jquery to bottle.py?
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?