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