How to Add Popup Menu Item in Excel/Word using VBA

Create Popup Menu (Right Click menu) using VBA

Here is a simple snippet that will add a menu item to the popup menu and assign a macro to it

Public Const APP_SHORTNAME = "VBADUD_POPUP"

Sub Add_To_Popup_Menu()

Dim ctlNewMenu As CommandBarControl

Dim ctlNewGroup As CommandBarControl

Dim ctlNewItem As CommandBarControl

On Error GoTo Err_Trap

On Error Resume Next

Application.CommandBars("Cell").Controls(APP_SHORTNAME).Delete

On Error GoTo 0

Set ctlNewMenu = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup)

ctlNewMenu.Caption = APP_SHORTNAME

'--- Button - Load Raw Data ------------

Set ctlNewItem = ctlNewMenu.Controls.Add(Type:=msoControlButton)

ctlNewItem.Caption = "Process Data"

ctlNewItem.OnAction = "ProcessData"

Err_Trap:

If Err <> 0 Then

Err.Clear

Resume Next

End If

End Sub

The above will create a new Group and add the “Process Data” control to it.

Add Combo Box to the command Bar

Add Combo Box to the command Bar

Sub Show_Combo_CommandBar()


Dim oCB As CommandBar
Dim oCtl As CommandBarComboBox

On Error Resume Next

'Delete Control From CommandBar
CommandBars("Sample Command Bar").Delete

Set oCB = CommandBars.Add
oCB.Name = "Sample Command Bar"
oCB.AdaptiveMenu = True

'Add Control to CommandBar
Set oCtl = oCB.Controls.Add(Type:=msoControlComboBox)
oCtl.Caption = "ComboSamp"

'Link Macro to CommandBar,
oCtl.OnAction = "Change_Header_Background"

'Add list Item to Combo Box Control
oCtl.AddItem "NoColor"
oCtl.AddItem "Blue"
oCtl.AddItem "Yellow"


' Show the Command Bar
oCB.Visible = True

' Place the CommandBar at the bottom of the screen
oCB.Position = msoBarBottom

End Sub

Sub Change_Header_Background()

' Acts based on the value in the Combo Box

Dim oCB As CommandBar
Dim oCtl As CommandBarComboBox

On Error Resume Next


Set oCB = CommandBars("Sample Command Bar")

Set oCtl = oCB.Controls("ComboSamp")

If oCtl.ListIndex <> -1 Then

Select Case oCtl.ListIndex

Case 1

ActiveSheet.Rows(1).Interior.ColorIndex = 0

Case 2

ActiveSheet.Rows(1).Interior.ColorIndex = 5

Case 3

ActiveSheet.Rows(1).Interior.ColorIndex = 36

Case Else

' Do nothing

End Select

End If


' Show the Command Bar
oCB.Visible = True

' Place the CommandBar at the bottom of the screen
oCB.Position = msoBarBottom


End Sub