200 Buttons All Changing One TextBox – VBA Excel

I have a VBA Excel application with about 200ish buttons. I want them all to change one textbox called “Name” with the caption of the button pressed. I did blur some stuff from the image as these boxes are all real peoples name. How can I do this without having to create 200+ functions?

An example is let’s say I have 3 buttons. The three values are foo1, foo2, and foo3. When I click any of the buttons, let’s say I click foo2, I want the TextBox to update the value to foo2. I know how to do this but how do I do it without having to manually write a function for all 3 buttons. The textbox value will always be the button value. Now imagine it is 200+ buttons.


Here is how I’d approach it. First add a class module and call it buttonClass:

Option Explicit

    Public WithEvents aCommandButton As msforms.CommandButton

    Private Sub aCommandButton_Click()
         MsgBox aCommandButton.Name & " was clicked"
    End Sub

Then in your userform initiation event, use code similar to:

Dim myButtons() As buttonClass

Private Sub UserForm_Initialize()
    Dim ctl As Object, pointer As Long
        ReDim myButtons(1 To Me.Controls.Count)

            For Each ctl In Me.Controls
                If TypeName(ctl) = "CommandButton" Then
                    pointer = pointer + 1
                    Set myButtons(pointer) = New buttonClass
                    Set myButtons(pointer).aCommandButton = ctl
                End If
            Next ctl

        ReDim Preserve myButtons(1 To pointer)

End Sub

This will wire up all of your command buttons to display their name on click. You can tweak the logic in the buttonClass to be fancier. Or you can tweak the logic in the initialize event to include on certain buttons (expanding on the “If” part).

Hope that helps!

If i was going to do this (and I’m not sure I would), I would create and populate each button through a loop, which also set up a link to one event handler which could determine what to do.

If each button is created manually and already exist, then I think you need to update them manually.

Another possibility, is to catch another higher-level event such as mouse click and then, from the information provided by that event, work out which button was pressed.