VBA Many buttons point to the same _Click sub

Question

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?

Answer 1

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

Answer 2

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)

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:

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

Hope it helps!

Answer 3

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

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

1. Create a Custom Class to 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 Public routine in a Standard Module to initialise a Collection of these Custom Class objects by spanning the Form Controls. You can also use this to Add the 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.

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.

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 article by @SiddharthRout. In line with the VB6 analogy, he uses an Array instead of a Collection to achieve the same result.

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.

Answer 4

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

Answer 5

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

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.

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.

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

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

Answer 6

Set the event to =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:

cmd1's On Click event:

=OpenDocumentById([DocID])

cmd2's On Click event:

=OpenDocumentById([DocID])

etc...