使用没有日期/时间格式的 Excel VBA 检查时间

Question

我试图确保该工具的用户只插入有效的时间值。此输入必须是数字，而不是日期时间格式。我知道前端的条件格式会简单得多，但是复制粘贴操作将覆盖条件格式。所以我决定使用以下逻辑为它编写一个宏： 1( 输入必须采用数字格式"0000" 2( 前 2 位数字必须小于 23 和 3( 最后 2 位数字必须小于 59

这是我到目前为止的代码，但它没有给我预期的输出。错误在哪里，或者有更有效的方法来解决这个问题？

Worksheets("SheetA").Range("A2:A" & lastRow).NumberFormat = "0000"
For count = 2 To (lastRow + 1)
If IsEmpty(Worksheets("SheetA").Range("A" & count).Value) = True Then
Worksheets("SheetA").Range("A" & count).Interior.Color = RGB(255, 255, 204)
Else
If (Left(Worksheets("SheetA").Range("A" & count).Text, 2)) > 23 Or (Right(Worksheets("SheetA").Range("A" & count).Text, 2)) > 59 Then
Worksheets("SheetA").Range("A" & count).Interior.Color = RGB(255, 0, 0)
End If
End If
Next count 

Answer 1

在普通模块中，创建一个过程来将数字格式化为文本并测试它在所需范围内：

Public Sub TestNumber(Target As Range)
Dim FormattedNumber As String
'Return formatted "text" version of value.
FormattedNumber = Format(Target, "0000")
'Assume the value is correct and set the cell to "valid" colour.
Target.Interior.Color = RGB(255, 255, 204)
'Validate the value - check it's length, that it's a number
'and falls with the specified ranges.
If Len(FormattedNumber) = 4 And IsNumeric(Val(FormattedNumber)) Then
If Val(Left(FormattedNumber, 2)) < 0 Or Val(Left(FormattedNumber, 2)) > 23 Or _
Val(Right(FormattedNumber, 2)) < 0 Or Val(Right(FormattedNumber, 2)) > 59 Then
Target.Interior.Color = RGB(255, 0, 0)
Else
'Remove if you don't want to reformat the number.
Target.Value = Format(FormattedNumber, "00:00")
End If
Else
Target.Interior.Color = RGB(255, 0, 0)
End If
End Sub

在要检查第1列中的数字的每张纸上，添加以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
If Not Intersect(Target, Columns(1)) Is Nothing Then
Application.EnableEvents = False
For Each rCell In Target
TestNumber rCell
Next rCell
Application.EnableEvents = True
End If
End Sub

仅当您在TestNumber过程结束时重新格式化输入的数字时，才需要Application.EnableEvents行。 不添加该命令会使用重新格式化的数字再次触发Change事件 - 这将返回不正确的结果，然后再次触发并最终返回00:00。

编辑：
已更新Change事件，以便在更改多个单元格(如果粘贴了一系列数字(并在TestNumber过程中将AND更改为OR(之前接受6000(时它将起作用。

再次编辑：
如果要检查输入的数字，请删除FormattedNumber变量并替换为Target并将列格式化为文本，以免丢失前导 0。

Answer 2

左函数(工作表/VBA(

Excel LEFT 函数既可以用作工作表函数，也可以用作 VBA 函数。LEFT 函数返回文本字符串中指定数量的字符，从第一个或最左边的字符开始。使用此函数从文本字符串的左侧提取子字符串。语法：左(text_string，char_numbers(。有必要提及text_string参数，它是要从中提取指定字符数的文本字符串。char_numbers 参数是可选的(用作工作表函数时(，它指定要从文本字符串中提取的字符数。char_numbers值应等于或大于零;如果它大于文本字符串的长度，LEFT 函数将完整返回文本字符串;如果省略，它将默认为 1。用作 VBA 函数时，必须指定两个参数，如果text_string包含 Null，则该函数也会返回 Null。

如果你想实现你的目标，我有一个选择给你： 您的输入必须是控件后的字符串，如果时间没有对话，则将字符串转换为数字。

要转换数字中的字符串示例：

Dim finalNumber As Integer
If IsNumeric(myVar) Then
finalNumber = CInt(myVar)
Else
finalNumber = 0
End If

并通过此步骤限制输入 https://www.extendoffice.com/documents/excel/952-excel-cell-character-limit.html