Excel VBA combobox on worksheet change event

Question

I am creating a set of comboboxes dynamically on an Excel sheet but I want just one generic "Action" method for them all. I get the data from one worksheet to populate the combos on the main worksheet

My code is below but I can't get access to each individual combobox when the event fires (it does fire). Is there a way to know which combobox has fired? Even if it's just the index, or name, or something, so I can then go and find the relevant combobox. (The number of combos is going to be 200 in total and a Form is not what we want here for other reasons which is why its in a sheet.)

Option Explicit

Dim i As Integer
' This is used to programme the comboboxes
Private Sub GenerateComboboxes()

    On Error GoTo ErrorHandler

    Dim DestinationDataTypeCombo As Object, DestinationDataTextCombo As Object, oObject As Object
    Dim ws As Worksheet, sString As String, rng As Range
    
    Dim nCombos, nStartLine As Integer
    Dim i2, iHeight, iLeft, iTop, iWidth As Integer
    
    nCombos = 5
    nStartLine = 2
    
    Set ws = Worksheets("User Entry")
    ws.Activate
    
    'Clear what was there before
    For Each oObject In ws.Shapes
        oObject.Delete
    Next
    
    ' add each combo to the worksheet
    For i = 0 To nCombos - 1
        sString = "D" & (i + nStartLine)
        Set rng = ws.Range(sString)
        
        ' Create a Combo instance
        Set DestinationDataTypeCombo = ws.Shapes.AddFormControl(xlDropDown, _
                                      Left:=rng.Left, _
                                      Top:=rng.Top, _
                                      Width:=rng.Width, _
                                      Height:=rng.Height)
                                      
        ' Set up the properties
        With DestinationDataTypeCombo
            .ControlFormat.DropDownLines = 5
            .Name = "cbDataType" & i
            For i2 = 2 To 17
                sString = Worksheets("Static Data").Cells(i2, 1)
               .ControlFormat.AddItem sString
                
            Next
            ' Set a generic OnAction for ALL combos to use
            .OnAction = "cbDataType_Change"
        End With
       
    Next i
    
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Description
     
End Sub
Public Sub cbDataType_Change()

    On Error GoTo ErrorHandler
    
    Dim sValue As String
    'This works so I know the change event fires
    MsgBox "Test"
    ' Want to get the value selected, this line errors
    sValue = cbDataType.Value
    MsgBox sValue
    
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Description
     
End Sub

Answer 1

Your code creates a Form Drop-Down Combo box. A Sheet Form control type does not expose any event...

You can assign a macro to it, named whatever you want (even cbDataType_Change), using .OnAction, as you did.

Now, the used object can be returned starting from the Application.Caller, which returns the control name. Based on it, the control object can be set, using Sheet.Shapes. And finally, the value of such a control can be retrieved in a little more complicated way, using the object OLEFormat.Object.list. So, your Sub assigned to all controls should be like this:

Sub cbDataType_Change() 'this is not an event!
    Dim cb As Object
    
    Set cb = ActiveSheet.Shapes(Application.Caller) 'the object which called this Sub
    Debug.Print cb.Name, cb.ControlFormat.Value     'returns the control name and its index
    MsgBox cb.OLEFormat.Object.list(cb.ControlFormat.Value) 'the control value
End Sub