Get the selected item of a dropdown in a custom menu (and run a macro accordingly)-CodePudding

I have this simple menu setup and I am trying to run a macro based on user selection of the label item from a dropdown list. The OnAction only works on the entire dropdown object and not executing macros per dropdown-list-item selection:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Dim oMenu As CommandBar
Set oMenu = CommandBars.Add("", msoBarPopup, , True)

Dim cbcm1 As CommandBarButton
Set cbcm1 = oMenu.Controls.Add(Type:=msoControlButton, Temporary:=True)
cbcm1.Caption = "Add new label"
cbcm1.OnAction = "AddNewLabel"

Dim cbcm2 As CommandBarComboBox
Set cbcm2 = oMenu.Controls.Add(Type:=msoControlDropdown, Temporary:=True)
    cbcm2.Caption = "Select label:"
        cbcm2.AddItem "NVPE"   ' << this should run a macro that adds a 'NVPE' in some other range..
        cbcm2.AddItem "COMP"   ' << this should run a macro that adds a 'COMP' in some other range.. and so on.
        cbcm2.AddItem "HOLD"
        cbcm2.AddItem "INPROG"
        cbcm2.AddItem "CANC"
    cbcm2.Width = 150
    cbcm2.ListIndex = 1 'default
    cbcm2.OnAction = "NewCommand_OnAction"
    cbcm2.Style = msoComboLabel

oMenu.ShowPopup  'display the menu
Cancel = True

End Sub

Any suggestions? I can't think of a way to get the selected index or value (caption) of the selected item.

Thanks!

CodePudding user response：

All you are missing is a Select Case in your event handler. Using your existing code, simply add the string cbcm2Text and set it to equal cbcm2.Text. Then, add the necessary Select Case prior to your Cancel = True statement to call your macros.
Example:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim oMenu As CommandBar
    Dim cbcm2Text As String
    Dim cbcm1 As CommandBarButton
    Dim cbcm2 As CommandBarComboBox
    Set oMenu = CommandBars.Add("", msoBarPopup, , True)
    Set cbcm1 = oMenu.Controls.Add(Type:=msoControlButton, Temporary:=True)
    Set cbcm2 = oMenu.Controls.Add(Type:=msoControlDropdown, Temporary:=True)
    cbcm1.Caption = "Add new label"
    cbcm1.OnAction = "AddNewLabel"
    cbcm2.Caption = "Select label:"
    cbcm2.AddItem "NVPE"
    cbcm2.AddItem "COMP"
    cbcm2.AddItem "HOLD"
    cbcm2.AddItem "INPROG"
    cbcm2.AddItem "CANC"
    cbcm2.Width = 150
    cbcm2.ListIndex = 1
    cbcm2.OnAction = "NewCommand_OnAction"
    cbcm2.Style = msoComboLabel
    oMenu.ShowPopup
    cbcm2Text = cbcm2.Text
    Select Case cbcm2Text
        Case "NVPE"
            Call NVPE
        Case "COMP"
            Call COMP
        Case "HOLD"
            Call HOLD
        Case "INPROG"
            Call INPROG
        Case "CANC"
            Call CANC
    End Select
    Cancel = True
End Sub
Sub NVPE()
    MsgBox "You selected NVPE"
End Sub
Sub COMP()
    MsgBox "You selected COMP"
End Sub
Sub HOLD()
    MsgBox "You selected HOLD"
End Sub
Sub INPROG()
    MsgBox "You selected INPROG"
End Sub
Sub CANC()
    MsgBox "You selected CANC"
End Sub

The preceding code produces the following result:

Here is the relevant Microsoft Documentation if you would like to see more examples of how to utilize combobox change events.