VBA用户窗体TextBox控件_BeforeUpdate等事件批量绑定失效求助

问题说明 #

需要给用户窗体中80个TextBox控件绑定统一的事件处理程序，参考代码实现了Change事件绑定，但BeforeUpdate、Exit等事件无法触发。

提供的代码 #

类模块（名称：mytextbox）

Private WithEvents txtbox As MSForms.TextBox

Public Property Set TextBox(ByVal t As MSForms.TextBox)
    Set txtbox = t
End Property

Private Sub txtbox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox "Worked in Exit"
    Cancel = True
End Sub

Private Sub txtbox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox "it worked in BeforeUpdate " & Me.Name
    Cancel = True
End Sub

用户窗体代码

Private myEventHandlers As Collection

Private Sub UserForm_Activate()
    Dim txtbox As mytextbox

    Set myEventHandlers = New Collection

    Dim c As Control
    For Each c In Me.Controls

        If c.Name Like "TextBox*" Then
            Set txtbox = New mytextbox

            Set txtbox.TextBox = c

            myEventHandlers.Add txtbox

        End If
    Next c

End Sub

问题原因及解决办法 #

• 事件触发条件未满足
  BeforeUpdate事件仅在TextBox内容被修改，且焦点离开该控件时才会触发；Exit事件在焦点离开TextBox时触发（无论内容是否修改）。测试时需先修改TextBox内容，再点击其他控件（如另一个TextBox、按钮）或尝试关闭窗体，才能触发对应事件。

• 代码细节修正
  类模块的txtbox_BeforeUpdate事件中，Me.Name指代的是类模块实例的名称，而非TextBox控件名称，若要显示控件名称，需改为txtbox.Name：

  Private Sub txtbox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
      MsgBox "it worked in BeforeUpdate " & txtbox.Name
      Cancel = True
  End Sub
  

• 控件属性检查
  确保目标TextBox的Enabled属性为True、Locked属性为False，否则控件无法编辑或获取焦点，会导致事件无法触发。

• 类模块名称验证
  确认类模块的名称确实是mytextbox，否则窗体代码中Dim txtbox As mytextbox会报错，导致事件绑定失败。

内容的提问来源于stack exchange，提问作者Lucas