禁用Text_Change事件VBA

Question

Dears，

有没有办法禁用Textbox_change事件？我尝试

Application.EnableEvents = False 

但它不起作用。

If .txtHomeNumber.Value <> "" Then
If IsNumeric(txtHomeNumber.Value) Then
.txtHomeNumber.BackColor = RGB(255, 255, 255)
.txtMobileNumber.BackColor = RGB(255, 255, 255)
.txtParentsNumber.BackColor = RGB(255, 255, 255)
Else: MsgBox "Please enter a valid Home Number in Contact Details section.", vbExclamation, "Contact Details"
Application.EnableEvents = False
.txtHomeNumber.Value = Left(.txtHomeNumber.Value, Len(.txtHomeNumber.Value) - 1)
Application.EnableEvents = True
Exit Sub
End If
End If

Answer 1

此代码在Userform上使用TextBox和CommandButton
我刚刚添加了按钮，这样您就可以在启用和禁用之间进行更改。

Option Explicit
Private bEnabled As Boolean
Private Sub UserForm_Initialize()
'Starts as FALSE so change to TRUE when form first opens.
bEnabled = True
End Sub
Private Sub TextBox1_Change()
If bEnabled Then
MsgBox "Enabled"
'        If .txtHomeNumber.Value <> "" Then
'            If IsNumeric(txtHomeNumber.Value) Then
'                .txtHomeNumber.BackColor = RGB(255, 255, 255)
'                .txtMobileNumber.BackColor = RGB(255, 255, 255)
'                .txtParentsNumber.BackColor = RGB(255, 255, 255)
'            Else: MsgBox "Please enter a valid Home Number in Contact Details section.", vbExclamation, "Contact Details"
'                Application.EnableEvents = False
'                    .txtHomeNumber.Value = Left(.txtHomeNumber.Value, Len(.txtHomeNumber.Value) - 1)
'                Application.EnableEvents = True
'                Exit Sub
'            End If
'        End If
End If
End Sub
Private Sub CommandButton1_Click()
bEnabled = Not bEnabled
End Sub

查看您的代码-您确定不想在AfterUpdate事件中使用它吗？

Answer 2

我通常定义自己的布尔值，如下所示：

Public DisableEvents As Boolean
Private Sub SomeObject_Change()
If Not DisableEvents Then
DisableEvents = True
' do_stuff
DisableEvents = False
End If
End Sub

这样可以防止您创建的无限循环。