VBA初始单元格更改无法激发函数-后续更改成功激发函数-Regex单元格验证

Question

下面的代码应该可以防止任何人输入非数字字符。

Private Sub Worksheet_Change(ByVal Target As Range)
Static X As Variant
Dim rng2 As Range
Dim rng3 As Range
If IsEmpty(X) Then X = [a1:a10].Value2
Set rng2 = Intersect([a1:a10], Target)
If rng2 Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rng3 In rng2
If Not isValidRegex(rng3, "d+") Then rng3.Value = X(rng3.Row, 1)
Next
Application.EnableEvents = True
X = [a1:a10].Value2
End Sub
Function isValidRegex(rng As Range, pattern As String) As Boolean
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.pattern = pattern
isValidRegex = re.Test(rng.Value)
End Function

问题是，当用户第一次尝试将A1:A10范围内的单元格更改为非数字字符时，isValidRegex函数无法启动。。。或者至少我认为这就是问题所在。也许这与变量X.有关

我希望Regex模式在任何时候都能强制执行。。。

Answer 1

也许以下代码适用于

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng2 As Range
Dim rng3 As Range
Set rng2 = Intersect([a1:a10], Target)
If rng2 Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rng3 In rng2
If Not isValidRegex(rng3, "d+") Then Application.Undo
Next
Application.EnableEvents = True
End Sub