VBA 很多按钮指向同一个_Click子

VBA Many buttons point to the same _Click sub

提问by PKeno

I have a bunch of TextBox-Button pairs on a form. When the button is clicked I want to insert the value of the text box into a database. The name TextBoxes and Buttons follow a naming standard, for example Value1Tb - Value1Cmd and Value2Tb - Value2Cmd.

我在表单上有一堆 TextBox-Button 对。单击按钮时，我想将文本框的值插入到数据库中。名称文本框和按钮遵循命名标准，例如 Value1Tb - Value1Cmd 和 Value2Tb - Value2Cmd。

My problem is that since I want to do the same for every button I would like the possibility to write a Sub like:

我的问题是，因为我想对每个按钮都做同样的事情，所以我希望可以编写一个 Sub ，例如：

Private Sub AnyButton_Click(sender As CommandButton)
  Dim tb As TextBox
  Set tb = GetTBByName(s.Name)
  PutValueToDatabase(s.Name,tb.Text)
End Sub

But I cannot find a way to point the Click-event of a Button to a different sub than the standard Name_Click().

但是我找不到将 Button 的 Click-event 指向与标准Name_Click().

Anybody know a way around this, that doesn't involve me writing 50 or so different Name_Click()subs?

任何人都知道解决这个问题的方法，这不需要我写 50 个左右不同的Name_Click()潜艇？

回答by Simon Cowen

If you are OK to use Form Controls rather that ActiveX, as it looks as though you may be at the moment, then Chris' solution seems good.

如果您可以使用 Form Controls 而不是 ActiveX，就像您现在看起来那样，那么 Chris 的解决方案似乎不错。

However if you need ActiveX CommandButtons then you are unable (as the VBA compiler will tell you, "Procedure declaration does not match...") to have parameters in the callback for the click event, and you are unable to raise the event from multiple objects, although you do of course know which button raised the event (since the relationship is 1 CommandButton = 1 Sub).

但是，如果您需要 ActiveX 命令按钮，那么您将无法（因为 VBA 编译器会告诉您“过程声明不匹配...”）在单击事件的回调中具有参数，并且您无法从多个对象，尽管您当然知道哪个按钮引发了事件（因为关系是 1 CommandButton = 1 Sub）。

So... I would go with something like:

所以......我会选择类似的东西：

Private Sub Value1Cmd_Click()
    Call TheMethod(Value1Cmd)
End Sub    

Private Sub Value2Cmd_Click()
    Call TheMethod(Value2Cmd)
End Sub


Private Sub TheRealMethod(sender As CommandButton)
    ' Do your thing '
    Dim tb As TextBox
    Set tb = GetTBByName(s.Name)
    PutValueToDatabase(s.Name,tb.Text)
    ' Etcetera... '
End Sub

Requires a stub for each button, so some copying and pasting to begin with, but then easy to maintain etcetera as all _Click event callbacks are pointing at the same method...

每个按钮都需要一个存根，因此开始时需要进行一些复制和粘贴，但随后易于维护等，因为所有 _Click 事件回调都指向相同的方法...

Edit: E.g.

编辑：例如

Sub AutoWriteTheStubs()
    Dim theStubs As String
    Dim i As Long
    For i = 1 To 10
        theStubs = theStubs & "Private Sub Value" & CStr(i) & "Cmd_Click()" & vbCrLf _
                   & "    Call TheMethod(Value" & CStr(i) & "Cmd)" & vbCrLf _
                   & "End Sub" & vbCrLf & vbCrLf
    Next i
    Debug.Print theStubs
End Sub

回答by weilah

It seems that what you want is to get the name of the clicked button. If you are creating buttons like this:

似乎您想要的是获取单击按钮的名称。如果您正在创建这样的按钮：

(where 'i' increments in a loop)

（其中 'i' 在循环中递增）

Set btn = Sheet1.Buttons.Add( , , , ,)
With btn
  .OnAction = "btnSub"
  .Caption = "Upadate"
  .Name = "btn" & CStr(i) & "Cmd"
End With

and then defining a generic "private sub btnSub()" for all the buttons, you could at least get the name of the button that was clicked using Application.Caller. Something like:

然后为所有按钮定义一个通用的“私有子 btnSub()”，您至少可以获取使用 Application.Caller 单击的按钮的名称。就像是：

Private Sub btnSub()
    Dim ButtonName As String
    ButtonName = Application.Caller
    MsgBox ("Hello:" & ButtonName)
End Sub

Hope it helps!

希望能帮助到你！

回答by Cool Blue

I decided to make this an answer because I am doing something similar and I confirmed that it works.

我决定将此作为答案，因为我正在做类似的事情并且我确认它有效。

You can store the OLEobjectsin a Collection, of arbitrary size, containing Custom ClassObjects that include the OLEobjects and associations and the events that you need. Thus you can completely avoid any code stubs.

您可以将 , 存储OLEobjects在Collection任意大小的 中，其中包含Custom Class包含 OLE 对象和关联以及您需要的事件的对象。因此，您可以完全避免任何代码存根。

1. Create a Custom Classto bind the Button and TextBox pairs.
2. Declare the Button object WithEvents.
3. Include your call-back in the exposed button event handler in the Class Module.
4. Put a Publicroutine in a Standard Moduleto initialise a Collectionof these Custom Classobjects by spanning the Form Controls. You can also use this to Addthe controls programmatically as a 'reBuild' option. The Collection can be inside another Class Module with all of the management routines, but it needs to be Instantiated and loaded in a Standard Module.
5. Put a public routine in a standard module to receive the call-backs with whatever context you need. This can also be in a Worksheet Module if it makes for better encapsulation. You can use late binding to reference the callback or CallByName.

1. 创建一个Custom Class来绑定 Button 和 TextBox 对。
2. 声明 Button 对象WithEvents。
3. 在类模块中公开的按钮事件处理程序中包含您的回调。
4. 将Public例程放在 a 中Standard Module以通过跨越表单控件来初始化Collection这些Custom Class对象中的一个。您还可以以Add编程方式将此作为“reBuild”选项用于控件。集合可以在另一个包含所有管理例程的类模块中，但它需要被实例化并加载到Standard Module.
5. 将公共例程放在标准模块中，以接收您需要的任何上下文的回调。如果它可以更好地封装，这也可以在工作表模块中。您可以使用后期绑定来引用回调或 CallByName。

You need to bear in mind that the Module of the Form will recompile every time you add a control, so you have to be careful where you put your code.

您需要记住，每次添加控件时，表单的模块都会重新编译，因此您必须小心放置代码的位置。

My application has the controls directly on the Worksheet Surface, so I can't put the the Collection Class in, or source any initialisation of the Collection from the Worksheet module. This would amount to self modifying code and it grinds excel to a halt.

我的应用程序直接在工作表表面上具有控件，因此我无法将集合类放入或从工作表模块中获取集合的任何初始化。这相当于自我修改代码，它会使 excel 停止。

I dreamed this idea up through bloody-minded idealism (not necessarily a good thing) but, of course, I was not the first one to think of it as you can see here. @Tim Williams explains it in his answer. You can also google VBA Control Array Events to see plenty of similar examples including an excellent articleby @SiddharthRout. In line with the VB6 analogy, he uses an Arrayinstead of a Collectionto achieve the same result.

我通过血腥的理想主义梦想这个想法（不一定是一件好事），但是，当然，正如你在这里看到的那样，我不是第一个想到它的人。@Tim Williams 在他的回答中解释了这一点。您还可以在 google VBA Control Array Events 中查看大量类似示例，包括@SiddharthRout 的一篇优秀文章。与 VB6 的类比一致，他使用 anArray代替 aCollection来实现相同的结果。

I'll try to post some code later. My application is a bit different so it will take a lot of work to trim it down, but the principle is the same.

稍后我会尝试发布一些代码。我的应用程序有点不同，所以需要大量的工作来修剪它，但原理是一样的。

The other thing to bear in mind is that VBE really struggles with this type of thing so don't worry if it is loading up you processors. After you re-start with VBE off, all will be fine.

另一件要记住的事情是 VBE 真的很难处理这种类型的事情，所以不要担心它是否会加载你的处理器。在关闭 VBE 的情况下重新启动后，一切都会好起来的。

回答by Lance Roberts

I have this same situation, and I just have a click event for every button that is a wrapper to the function I want to call. This also allows you to pass sheet-specific parameters if you need to.

我有同样的情况，我只有一个单击事件，每个按钮都是我要调用的函数的包装器。如果需要，这还允许您传递特定于工作表的参数。

Example:

例子：

Public Sub StoreButton_Click()

' Store values for transaction sheet 3/27/09 ljr

Call StoreTransValues(ActiveSheet)

End Sub

回答by Fran?ois Bonnefoi

I just published (Open Source) the Event Centralizer for MSForms.

我刚刚发布了（开源）MSForms的Event Centralizer。

Citation: "The Event Centralizer for MSForms is a VBA programming tool that allows all sorts of custom grouping when writing handlers for the events occurring in UserForms.

引用：“MSForms 的事件集中器是一个 VBA 编程工具，它允许在为用户窗体中发生的事件编写处理程序时进行各种自定义分组。

With the Event Centralizer for MSForms, it is easy for example to have all TextBoxes react the same way when the Enter event occurs, or all except one, or only those with a particular Tag value.

使用 MSForms 的事件中心化器，很容易让所有文本框在 Enter 事件发生时以相同的方式做出反应，或者除了一个之外的所有文本框，或者只有那些具有特定标签值的文本框。

Thanks to its events logs system, the Event Centralizer for MSForms is a powerful learning and debugging help."

由于其事件日志系统，MSForms 的事件集中器是一个强大的学习和调试帮助。”

I can't explain here how it works. I tried to do it on the site.

我无法在这里解释它是如何工作的。我试图在网站上做到这一点。

回答by Yale

Set the event to =FunctionName(parameter).

将事件设置为=FunctionName(parameter)。

A bit late but this may help someone else:

有点晚了，但这可能对其他人有所帮助：

If you have a function called OpenDocumentById(docID as integer), then each control calling the function would have in the event the following:

如果您有一个名为 的函数OpenDocumentById(docID as integer)，则调用该函数的每个控件在以下情况下都将具有：

cmd1's On Click event:

cmd1 的点击事件：

=OpenDocumentById([DocID])

cmd2's On Click event:

cmd2 的点击事件：

=OpenDocumentById([DocID])

etc...

等等...