如何获取用户在excel中输入单元格的确切文本

I should start by saying that I know no VBA

我应该先说我不懂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).

我必须在excel中输入很多时间值。因为在输入时我真的放慢了进入冒号的速度,我想我会编写一个允许我输入类似“18.10”的字符串并将其转换为“18:10”的宏。 (然后我可以使用小键盘并快速输入时间)。

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 treating it as a number and trimming trailing 0s.

我拼凑了将任何给定(分隔)字符串转换为时间的函数 - 这很容易。但是现在我遇到了我的事件处理程序的问题,因为我无法得到我输入的确切文本,因为Excel似乎将其视为数字并修剪尾随0。

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

例如。 18.10被转换为18.1 - 它被转换为18:01(而不是我想要的18:10)。

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

这是我的更改处理代码(从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

EndMacro:MsgBox“您没有输入有效时间”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?

正如您所看到的,我正在使用Text - 我在调试器中查看了Target上的所有属性,但找不到任何看起来像是原始输入文本的属性。有没有办法获得价值?

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.

谢谢Issun和Craig。 Issun是正确的 - 我使它过于复杂(w.r.t.转换为时间)。这就是我提出的并且工作得很好的原因。

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

正如评论中提到的,我发现将单元格格式更改为“0.00”效果很好。输入为“18.1”的时间转换为“18:10”(而不是18:01)但实际上我可以这样做。

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.

我分配给几个热键的最后两个宏,这样如果我确实犯了错误,我可以快速将单元格格式转换回数字并再次结束该值。

2 个解决方案

#1

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.

如果将单元格的格式更改为文本(格式化单元格 - >文本),这将保留18.10并允许您更轻松地解析值。

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.

然后,应该将单元重新格式化为“时间”格式。

#2

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.

如果要做的只是将18.10更改为18:10,则单击工作表的左上角以选择所有单元格,然后将格式更改为文本。然后输入并搜索并替换“。”当你完成时,“:”

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:

如果您愿意,这里有一种有趣的方式可以自动格式化您输入的内容。在VBA编辑器中,双击Sheet1或您正在处理的任何工作表,然后输入以下代码:

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.

这是一个发生了什么的例子。一个注意事项:对于xx:30,您需要输入一个额外的“。”这样Excel就不会混淆了。

1.15 will become 1:15 AM

1.15将成为凌晨1:15

13.15 will become 1:15 PM

13.15将成为下午1:15

16:30. will become 4:30 PM

16:30。将成为下午4:30

#1