How to replace simplify code when calling command bar combobox

Question

I have this code in a class module

Public WithEvents evtHandler As VBIDE.CommandBarEvents

Private Sub evtHandler_Click(ByVal CommandBarControl As Object, _
    handled As Boolean, CancelDefault As Boolean)
    
    On Error Resume Next
    Application.Run CommandBarControl.OnAction
    
    handled = True
    CancelDefault = True
End Sub

I have this code in a module

Sub CommandBarComboBox_Create()
    Dim cmdBar As commandBar: Set cmdBar = Application.VBE.CommandBars("Custom")
    Dim arr As Variant: arr = Array("item_1", "item_2", "item_3")
    
    Dim i As Byte
    Dim newCtrl As CommandBarControl: Set newCtrl = cmdBar.Controls.Add(msoControlComboBox)
    
    With newCtrl
        .caption = "myList"
        For i = LBound(arr) To UBound(arr)
            .AddItem arr(i), i + 1
        Next i
        .OnAction = "'processSelection'"
        .tag = "myTag"
    End With

    ' Create event handler
    Set MenuEvent = New VBE_cmdHandler
    Set MenuEvent.evtHandler = Application.VBE.Events.CommandBarEvents(newCtrl)
    EventHandlers.Add MenuEvent
End Sub

Sub processSelection()
    Dim cmdBar As commandBar: Set cmdBar = Application.VBE.CommandBars("Custom")
    Dim userChoice As Long: userChoice = cmdBar.Controls(2).ListIndex
    
    Select Case userChoice
        Case 1
            Debug.Print "OPTION 1"
        Case 2
            Debug.Print "OPTION 2"
        Case Else
            Debug.Print "OPTION 3"
    End Select
End Sub

This works just fine but I would like to change it, so when sub processSelection is called, it implicit knows the calling control, so I don't have to specify it.

How can it be done?

Regards Elio Fernandes

score 0 · Accepted Answer · answered Feb 25 '21 at 17:17

I just found a way to solve my problem, using the tag property of the control.

I replaced the first 2 lines of code of the sub processSelecion with 3 new lines, but without the need to identify with command bar and which control was last used.

With tag property, I can find which control was last used and consequently which ListIndex item was selected by the user.

Sub processSelection()
    Dim curTag$: curTag = Application.VBE.CommandBars.ActionControl.tag
    Dim myControls As Object: Set myControls = Application.VBE.CommandBars.FindControls(tag:=curTag$)
    Dim userChoice As Long: userChoice = myControls.item(1).ListIndex
    
    Select Case userChoice
        Case 1
            Debug.Print "OPTION 1"
        Case 2
            Debug.Print "OPTION 2"
        Case 3
            Debug.Print "OPTION 3"
        Case Else
            Debug.Print "OTHER"
    End Select
End Sub

How to replace simplify code when calling command bar combobox

1 Answers1