EXCEL VBA 宏 如何动态向控件添加事件监听过程 2021年4月8日
EXCEL VBA 自动化
127 条评论

 


方式一:首先定义类mycell,添加Lable到自定义的事件处理过程
Private WithEvents m_Cell As MSForms.Label
 
Public Sub AddCell(ByVal cell As MSForms.Label) //初始化,将目标控件加入监听
    Set m_Cell = cell
End Sub
 
Private Sub m_Cell_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) //自定义事件处理
    m_Form.CellMouseDown m_Cell, Button, Shift, X, Y
End Sub
主类中处理:
Public Cell As New Mycell // 应为公共变量,以保存

cell.AddCell lable1



方式二:VBA EXCEL 通过动态代码,添加Lable到自定义的事件处理过程

首先创建控件并选中
Application.ScreenUpdating = False //停止更新
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
                               DisplayAsIcon:=False, Left:=100, Top:=50, Width:=80, Height:= _
                                                                                            20).Select
dim CtlName = Selection.Name  //获得控件名

Dim MyCode(3) As String  //定义代码行

      	 MyCode(1) = "Private Sub " & CtlName & "_Change()"
        MyCode(2) = CtlName & ".Caption=" & CtlName & ".value"
        MyCode(3) = "End Sub"  // 编写动态代码
        For i = 1 To 3 //执行动态代码
            ThisWorkbook.VBProject.VBComponents(Me.CodeName).CodeModule.InsertLines i, MyCodeLine(i)
        Next
	 Application.ScreenUpdating = True //恢复更新