在Excel中使用VBA访问表单控件的事件过程 [英] Accessing Event Procedures of Form Controls with VBA in Excel

问题描述

有没有办法定义表单控件的事件过程，例如ActiveX对象的情况？

我有一个GUI，当前添加/删除ActiveX命令按钮该用户却遇到错误91 当尝试添加一个项目到全局集合添加每个按钮。我最好的猜测是因为这个很讨厌的现象。使用项目的当前状态，如果能够定义其事件过程，我最好的选择是切换到使用表单控件按钮。如果没有，我可能需要以某种方式保存全局变量并在操作ActiveX控件对象后读取它们。

解决方案

如果您只想添加按钮并捕获点击，则可以为其分配一个通用宏，并切换哪些操作基于按钮的名称（在创建时您将设置它的名称，以及可以通过调用过程中的 Application.Caller 访问）。

  Sub AddButtons（）
 
 With ActiveSheet.Buttons.Add（100，100，50，50）
 .Name =button1
 .OnAction =ClickHandler
 End with 
 With ActiveSheet.Buttons.Add（200，100，50，50）
 .Name =button2 
 .OnAction =ClickHandler
结束
 
 
 End Sub 
 
 Sub ClickHandler（）
 Dim bName As String 
 
 bName = Application.Caller 
选择案例bName 
案例button1：MsgBox点击第一个按钮
案例button2：AnotherSub 
结束选择
 
 End Sub 
  

Is there any way to define event procedures for form controls such as the case with ActiveX objects?

I have a GUI which currently adds/removes ActiveX command buttons by the user but ran into Error 91 when trying to add an item to a global collection with each button added. My best guess is because of this much hated phenomenon. With the current state of the project, my best option is to switch to using form control buttons if I am able to define their event procedures. If not, I may need to somehow save the global variables and read them back after manipulating ActiveX control objects.

解决方案

If you just want to add buttons and capture clicks, you can assign a common macro to them, and switch what action is taken based on the name of the button (which you would set when you create it, and which can be accessed via Application.Caller in the called procedure)

Sub AddButtons()

    With ActiveSheet.Buttons.Add(100, 100, 50, 50)
        .Name = "button1"
        .OnAction = "ClickHandler"
    End With
    With ActiveSheet.Buttons.Add(200, 100, 50, 50)
        .Name = "button2"
        .OnAction = "ClickHandler"
    End With


End Sub

Sub ClickHandler()
    Dim bName As String

    bName = Application.Caller
    Select Case bName
        Case "button1": MsgBox "Clicked First button"
        Case "button2": AnotherSub 
    End Select

End Sub

这篇关于在Excel中使用VBA访问表单控件的事件过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！