200 buttons All changes to one TextBox - VBA Excel
I have a VBA Excel application with about 200 buttons. I want them all to change one textbox called "Name" with the title of the button. I blurred some things with the image as these boxes are all real names of people. How can I do this without creating more than 200 functions?
Three buttons are an example. Three values foo1
, foo2
and foo3
. When I click any of the buttons, say I click foo2
, I want the TextBox to update the value to foo2
. I know how to do this, but how to do it without having to manually write a function for all 3 buttons. The textbox value will always be the button value. Now imagine that there are 200+ buttons.
Thank!
source to share
This is how I approach it. First add a class module and name it buttonClass
:
Option Explicit
Public WithEvents aCommandButton As msforms.CommandButton
Private Sub aCommandButton_Click()
MsgBox aCommandButton.Name & " was clicked"
End Sub
Then, in your initiation of your custom form, 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 when clicked. You can customize the logic in buttonClass to be more attractive. Or you can customize the logic in the initialize event to include on specific buttons (extension on the "If" part).
Hope it helps!
source to share
If I was going to do this (and I'm not sure if I will), I would create and fill each button through a loop that also set a link to one event handler that could figure out what to do.
If each button is created manually and already exists, I think you need to update them manually.
Another possibility is to catch another higher-level event, such as a mouse click, and then, from the information provided by that event, determine which button was pressed.
source to share