如何用VBA类模块WithEvents为Access控件绑定鼠标事件？

问题场景 #

尝试通过带WithEvents的VBA类模块为Access命令按钮（标签、文本框等控件通用）动态绑定MouseMove、MouseUp和MouseDown事件，但仅Click事件可正常触发，鼠标相关事件无响应且无报错。

核心原因 #

Access中，仅设置控件的OnClick = "[Event Procedure]"只能关联Click事件到类模块的处理函数，鼠标类事件需要通过控件的EventProcPrefix属性指定事件处理的前缀，以此关联类中对应前缀的事件过程，而非单独设置每个事件的On...属性。

修复方案 #

修改类模块（clsButtonHolder） #

移除原AddControl函数中仅针对Click事件的设置，改为通过EventProcPrefix统一关联所有事件：

Option Compare Database
Option Explicit

' Class Module: clsButtonHolder
Public WithEvents ButtonControl As Access.CommandButton
Private x_offset As Integer, y_offset As Integer

Public Sub AttachControl(ctrl As Access.CommandButton)
    Set ButtonControl = ctrl
    ' 设置事件过程前缀，让控件关联类中ButtonControl_xxx格式的事件处理
    ctrl.EventProcPrefix = "ButtonControl"
End Sub

Public Sub ButtonControl_Click()
    Debug.Print "Click triggered"
End Sub

Public Sub ButtonControl_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Debug.Print "MouseDown triggered"
End Sub

Public Sub ButtonControl_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Debug.Print "MouseMove triggered"
End Sub

Public Sub ButtonControl_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Debug.Print "MouseUp triggered"
End Sub

修改窗体模块 #

确保类实例被模块级Collection持有，避免垃圾回收导致事件失效：

Option Compare Database
Option Explicit

Dim ButtonHolders As Collection

Private Sub btnInit_Click()
    Dim holder As clsButtonHolder
    Dim cmdBtn As Access.CommandButton
    
    ' 初始化Collection（避免重复创建）
    If ButtonHolders Is Nothing Then
        Set ButtonHolders = New Collection
    End If

    Set holder = New clsButtonHolder
    Set cmdBtn = Me.btnTest
    holder.AttachControl cmdBtn
    
    ButtonHolders.Add holder
End Sub

关键说明 #

• EventProcPrefix是核心：该属性指定后，Access会自动将控件的所有事件与类中[前缀]_[事件名]格式的过程关联，无需单独设置每个On...属性。
• 类实例必须被持久化持有：使用模块级Collection存储类实例，防止局部变量销毁导致事件绑定失效。

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