Customize right click on page tabs

[andrewb90]

Hello all,

I have been able to customize the right click menu on various cells in my workbook, but I was wondering if it were possible to customize the right click menu that appears when you click on any page tab. Is this possible?

Any help would be greatly appreciated. I'll attach the code I am currently using, perhaps there is just a simple change I can make.

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

Dim ContextMenu As CommandBar
Dim ctrl As CommandBarControl


Application.CommandBars("Cell").Reset


If Intersect(Target, Range("E89:R106,E108:R125,E127:R144,E146:R163,E194:R207,E165:R165,E167:R167,E169:R169,E171:R171,E173:R173,E175:R175,E177:R177,E179:R179,E181:R181,E183:R183,E185:R185,E187:R187,E189:R189,E191:R191")) Is Nothing Then
    Application.CommandBars("Cell").Reset
    Exit Sub
Else


    Set ContextMenu = Application.CommandBars("Cell")


    For Each ctrl In ContextMenu.Controls
            ctrl.Delete
    Next ctrl
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO"
        .FaceId = 2113
        .Caption = "***REQUEST TIME OFF***"
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=2)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_OTHER"
        .FaceId = 1845
        .Caption = "WORK AT OTHER STORE"
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=3)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_JURY"
        .FaceId = 2131
        .Caption = "**JURY DUTY**"
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=4)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_MATERNITY"
        .FaceId = 2777
        .Caption = "*MATERNITY LEAVE*"
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=5)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_EVENT"
        .FaceId = 353
        .Caption = "BUSINESS/WORK EVENT"
    End With
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=6)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_NOTE"
        .FaceId = 916
        .Caption = "Add a custom note to the schedule"
    End With
 'MAYBE MOVE THE IF() SO THAT THE OTHER R/O TYPE IS ABOVE THE CLEAR REQUESTS LINE WHEN IN USE
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=7)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "CLEAR"
        .FaceId = 2087
        .Caption = "*!CLEAR REQUESTS!*"
    End With
    If Sheets("Initial").Range("C46").Value = "" Then
        Exit Sub
        'Sheets("Print").Range("Y3") & ": " &
    Else:
        With ContextMenu.Controls.Add(Type:=msoControlButton, before:=8)
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_CUSTOM"
            .FaceId = 484
            .Caption = Sheets("Initial").Range("C46") & " " & Sheets("Initial").Range("D46")
        End With
    End If
End If


End Sub

 

[mikerickson]

You have code that modifies CommandBars("Cell")
Right clicking on a sheet's tab displays CommandBars("Ply"). I note that the first control on that command bar (Ungroup Sheets) is not always visible.

 

[andrewb90]

So if I wanted to disable the rightclick entirely I would not put reset at the end, but disable?

 

[mikerickson]

If you want to customize the menu that appears when you right click a sheet tab, you could use code like

Sub test()
    With CommandBars("ply")
        On Error Resume Next
        .Controls("custom").Delete
        On Error GoTo 0
        With .Controls.Add(Type:=msoControlButton, Temporary:=True)
            .Caption = "custom"
            .Style = msoButtonCaption
            .OnAction = "mySub"
        End With
    End With
End Sub

If you were to set the Enabled of that command bar to False, you would want to set the Enabled to True in the Workbook_BeforeClose, the Workbook_Open and the Workbook_Deactivate events. (Set Enabled back to False in the Workbook_Activate event)