Add Control to List On Right-Click

[Rasm]

All this code is posted on a different thread -(Excel Fox - calendar control - under Ribbon code - fluent interface) - But I figured it was worth posting separately - what this code does - it adds to the list when you Right_Click on any cell on any sheet - I have used the workbook event. But you can also use the WorkSheet event - if you only want action on a single sheet.

If you look in Excel Fox's thread - there is a link to all the various .FaceId you can use - I just messed around in the code to test it.


ThisWorkBook

Option Explicit

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    AddOnRightClick
End Sub

Private Sub AddOnRightClick()
    On Error Resume Next
 
    Dim cbrButton As CommandBarButton
    
    With Application
        .CommandBars("Cell").Controls("Sheet_Button_Testing").Delete
        Set cbrButton = .CommandBars("Cell").Controls.Add(Temporary:=True, Before:=1)
    End With
 
    With cbrButton
       .BeginGroup = True
       .Style = msoButtonIconAndCaption
       .Caption = "Sheet_Button_Testing"
       '.FaceId = 125
       '.FaceId = 17
       '.FaceId = 99
       .FaceId = 519
       .OnAction = "ShowRightClicked"
    End With
 
    Set cbrButton = Nothing
 
    On Error GoTo 0
End Sub

Module1

Option Explicit
Public Sub ShowRightClicked()
    MsgBox "Right_Click captured - add your own code here"
End Sub