我有這個簡單的選單設定，我正在嘗試根據用戶從下拉串列中選擇標簽項來運行宏。OnAction 僅適用于整個下拉物件，不執行每個下拉串列項選擇的宏：
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
有什么建議么？我想不出一種方法來獲取所選專案的所選索引或值（標題）。
謝謝！
uj5u.com熱心網友回復：
您所缺少的只是Select Case事件處理程式中的一個。使用您現有的代碼，只需添加字串cbcm2Text并將其設定為 equal cbcm2.Text。然后，Select Case在您的宣告之前添加必要的Cancel = True內容以呼叫您的宏。
例子：
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
前面的代碼產生以下結果：
如果您想查看更多有關如何利用組合框更改事件的示例，請參閱相關Microsoft 檔案。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/521998.html
標籤：vba菜单栏命令栏