In VBA, is there a global Frame Event that could react to a click on any of its contained object?

Question

Within EXCEL VBA a form is created containing 2 frames. In each frame there are 20 control buttons. For a total of 40 control buttons on that form. Each gang of 20 controls have a simple task to define the value of a global string called strLocation.

The strLocation can then have a total of 40 different string values. Each button has one single code line like such as:
strLocation= “Something”

The form is displayed only when the user click on some particular cells in a sheet for which some conditions define if the form will be displayed. If the condition of the cell is not met, the form remain hidden and the user can just continue to other tasks. If the condition in met, the form is displayed, showing 40 buttons within 2 frames.

The user task is to select one of the 40 buttons and the strLocation will be defined. The global event ( when any one of the 40 buttons is clicked ) would then hide the form and set the cell value to the strLocation string value. All this is working fine except that I cannot find how to trigger an event that would react to any of the 40 buttons. I’d like to implement something like this in the form code:

Private Sub ( one event that would encompasses any of the 40 buttons being clicked )
    Selection = strLocation
    formLOCATION.Hide
End Sub

Of course I could include an extra 40 lines, one on each button code, calling one single Sub that would do the job. I suspect there exist a more elegant way of capturing a global event that would be related to each button included in the form or included in each frame.

Since a frame is a global container for all its objects, is there not an event that exist to capture a click on any of its contained object?

Something similar than what is available in an EXCEL Worksheet capturing any Cell click, or EXCEL Workbook capturing any Worksheet SheetActivate event.

Answer 1

You could use a custom class to create a "control array" of all of the buttons in a frame. See (eg) https://bettersolutions.com/excel/macros/vba-control-arrays.htm

Something like this:

In the worksheet code module - showing the form if a cell is selected in C3:C13

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim frm As frmSelect, c As Range
    
    If Target.Cells.CountLarge > 1 Then Exit Sub
    Set c = Application.Intersect(Me.Range("C3:C13"), Target)
    If c Is Nothing Then Exit Sub
    
    Set frm = New frmSelect  'create userform
    Set frm.theCell = c      'assign the selected cell
    frm.Show                 'show the form
    Unload frm
End Sub

The userform frmSelect

Option Explicit

Dim rng As Range
Dim col As Collection 'must be Global (to the form)

Private Sub UserForm_Activate()
    Dim con As Object, evt As Object
    
    Set col = New Collection
    For Each con In Me.Frame1.Controls 'buttons are in Frame1
        Set evt = New clsButton        'assuming all contained controls are buttons...
        evt.Init con, Me               'set up the class instance
        col.Add evt                    'store the object in the collection
    Next con
End Sub

'the cell to be populated
Property Set theCell(c As Range)
    Set rng = c
End Property

'called from the event class on clicking a button
Public Sub SetCell(txt As String)
    rng.Value = txt
    Me.Hide
End Sub

Finally the class clsButton to manage the events

Option Explicit

Private WithEvents btn As MSForms.CommandButton
Private frm As frmSelect

'set up the class instance
Public Sub Init(objBtn As MSForms.CommandButton, objFrm As Object)
    Set btn = objBtn
    Set frm = objFrm
End Sub

'handle the click event
Private Sub btn_Click()
    frm.SetCell btn.Caption 'call Sub in the form
End Sub