Kapat

Excel Customize Context(Right Click) Menu

excel  vba sample Add Menu Item To Right
Click Menu

If needed new items can be added to the cell context (also called the right click menu or shortcut menu) menu that Excel offers us . In our template, we used VBA codes to add new item to Context menu.

Through the new items that we added in the context menu ;
✔️ Text in cell can be converted easily to uppercase,lowercase or propercase. excel add item to context menu

excel right click menu
✔️ The selected sheet from the list of sheet names that displayed on the popup list can be navigated. excel add item to right click menu

Application.CommandBars("Workbook Tabs").ShowPopup on the context menu
✔️ Row height can be changed using inputbox.

vba change row height with inputbox
✔️ Column width can be changed using inputbox.
excel change column width with inputbox

To customize the context menu, we opened Visual Basic Editor and added a Module. We created AddItemContextMenu procedure in module that we added. In this procedure, we have added VBA codes for new items that we want to add to the cell context (right click shortcut) menu.

Sub AddItemContextMenu()
    Dim ContextMenu As CommandBar
    Dim New_Sub_Menu As CommandBarControl

    ' If context menu has been created before, let's delete it to avoid duplicates.
    Call DeleteItemContextMenu

    ' Set ContextMenu variable for the cell context menu.
    Set ContextMenu = Application.CommandBars("Cell")

    ' Let's add a submenu with three buttons.
    Set New_Sub_Menu = ContextMenu.Controls.Add(Type:=msoControlPopup, before:=1)

    With New_Sub_Menu
    
        .Caption = "Changing Case"
        .Tag = "New_Item_Context_Menu"

        With .Controls.Add(Type:=msoControlButton)
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "UpperMacro"
            .FaceId = 100
            .Caption = "Upper Case"
        End With
        With .Controls.Add(Type:=msoControlButton)
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "LowerMacro"
            .FaceId = 91
            .Caption = "Lower Case"
        End With
        With .Controls.Add(Type:=msoControlButton)
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "ProperMacro"
            .FaceId = 95
            .Caption = "Proper Case"
        End With
    End With

    '----------------------------------------------------------------------------
    ' New button for context menu.
       With ContextMenu.Controls.Add(Type:=msoControlButton, before:=2)
     .OnAction = "'" & ThisWorkbook.Name & "'!" & "Index_Code"
     .FaceId = 1087
     .Caption = "Sheet Index"
     .Tag = "New_Item_Context_Menu"
     End With
     
     ' New button for context menu.
      With ContextMenu.Controls.Add(Type:=msoControlButton, before:=3)
     .OnAction = "'" & ThisWorkbook.Name & "'!" & "Changing_row_height"
     .FaceId = 39
     .Caption = "Change Row Height"
     .Tag = "New_Item_Context_Menu"
     End With
    
    ' New button for context menu.
     With ContextMenu.Controls.Add(Type:=msoControlButton, before:=4)
     .OnAction = "'" & ThisWorkbook.Name & "'!" & "Changing_column_width"
     .FaceId = 39
     .Caption = "Change Column Width"
     .Tag = "New_Item_Context_Menu"
     End With
    ' Let's add a separator to context menu.
ContextMenu.Controls(5).BeginGroup = True
End Sub
...
Sub Index_Code()
If ActiveWorkbook.Sheets.Count > 16 Then
  Application.CommandBars("Workbook Tabs").Controls("More    Sheets…").Execute
Else
Application.CommandBars("Workbook Tabs").ShowPopup
End If
End Sub
...

When we examine the codes above, we see that a CommandBar object named ContextMenu has been created. This CommandBar object is used when adding a new item to the Context menu. excel vba commandbar
Set ContextMenu = Application.CommandBars("Cell")
With ContextMenu.Controls.Add(Type:=msoControlButton, before:=2)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "Index_Code"
.FaceId = 1087
.Caption = "Sheet Index"
.Tag = "New_Item_Context_Menu"
End With

OnAction code indicates that the Index_Code procedure will be run when the button is selected.

We created DeleteItemContextMenu procedure to delete menu items that we just added when the workbook is closed and return to default settings. excel vba commandbar reset

Sub DeleteItemContextMenu()
    Dim ContextMenu As CommandBar
    Dim ctrl As CommandBarControl

    ' Set ContextMenu for cell context menu.
    Set ContextMenu = Application.CommandBars("Cell")

    ' Delete the custom controls with the Tag : New_Item_Context_Menu.
    For Each ctrl In ContextMenu.Controls
        If ctrl.Tag = "New_Item_Context_Menu" Then
            ctrl.Delete
        End If
    Next ctrl

End Sub

When the workbook is opened ,to run context menu that we customized it, we called the AddItemContextMenu procedure in Workbook_Activate method .
We call the DeleteItemContextMenu procedure in Workbook_Deactivate method to reset (to remove items that we added) the context menu when the workbook is closed. excel vba context menu reset

Private Sub Workbook_Activate()
    Call AddItemContextMenu
End Sub

Private Sub Workbook_Deactivate()
    Call DeleteItemContextMenu
End Sub

📥 Download sample workbook that contained the customized context menu