Add Control to List On Right-Click

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

Code:

---

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

Code:

---

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

---