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.