VBA许多按钮指向相同的_Click子 [英] VBA Many buttons point to the same _Click sub

问题描述

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

我的问题是，因为我想为每个按钮做同样的事情想要写一个Sub的可能性：

  Private Sub AnyButton_Click（sender As CommandButton）
 Dim tb As TextBox 
设置tb = GetTBByName（s.Name）
 PutValueToDatabase（s.Name，tb.Text）
 End Sub 
  

但是我找不到一种方法来将Button的Click事件指向不同于标准的 Name_Click（）

任何人都知道这一点，这不涉及我写50个不同的 Name_Click（）

解决方案

如果可以使用Form Controls而不是ActiveX，因为它看起来好像您可能在此刻那么克里斯的解决方案似乎很好。但是如果您需要使用ActiveX CommandButton，那么您无法使用VBA编译器来告诉您，过程声明不符合...）在对于点击事件的回调，您无法从多个对象引发事件，尽管您当然知道哪个按钮引发事件（因为关系为1 CommandButton = 1 Sub）。

所以...我会去这样的东西：

  Private Sub Value1Cmd_Click（）
调用TheMethod（Value1Cmd）
 End Sub 
 
 Private Sub Value2Cmd_Click（）
调用TheMethod（Value2Cmd）
 End Sub 
 
 
 Private Sub TheRealMethod（sender As CommandButton）
'做你的东西'
 Dim tb As TextBox 
 Set tb = GetTBByName（s.Name）
 PutValueToDatabase（s.Name，tb .Text）
'Etcetera ...'
 End Sub 
  

需要一个stub为每个按钮，所以一些复制和粘贴开始随着所有_Click事件回调指向相同的方法，但是然后容易维护等等。

编辑：
例如

  Sub AutoWriteTheStubs（）
 Dim theStubs As String 
 Dim i As Long 
 For i = 1 To 10 
 theStubs = theStubs& 私人子值& CStr（i）& Cmd_Click（）& vbCrLf _ 
& Call TheMethod（Value& CStr（i）&Cmd）& vbCrLf _ 
& End Sub& vbCrLf& vbCrLf 
 Next i 
 Debug.Print theStubs 
 End Sub 
  

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.

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

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().

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

解决方案

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.

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).

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...

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

这篇关于VBA许多按钮指向相同的_Click子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！