** Resolved ** custom right click dropdown menu button

[big_k105]

is this even possible to do? im sure it is possible to add buttons the the right click drop down menu in Excel. i need to have the user right click on a cell and then in the dropdown menu there be a custom button the activate a vba program or macro. thanks for the help in advanced

[WorkHorse]

The popup CommandBar that shows when you right click a cell is called "Cell". We will add/remove a Control to that CommandBar when the workbook is activated/deactivated so that the custom button only shows in your workbook. The custom button runs a sub in a module.

In a woorkbook:

Private Sub Workbook_Activate()
 
    Dim cbc As CommandBarControl
    Dim blnControlFound As Boolean
 
    ' Check if custom control has already been added to Cell popup.
    For Each cbc In Application.CommandBars("Cell").Controls
        If cbc.Caption = "Run My Macro" Then
            blnControlFound = True
            Exit For
        End If
    Next cbc
 
     'Add custom control to Cell popup if not already added.
    If blnControlFound <> True Then
        With Application.CommandBars("Cell").Controls.Add
            .BeginGroup = True          ' Start a new group.
            .FaceId = 346               ' Set display picture.
            .Caption = "Run My Macro"   ' Set display text.
            .OnAction = "MyMacro"       ' Set macro to run.
        End With
    End If
 
End Sub
 
Private Sub Workbook_Deactivate()
 
    ' Delete the custom control From Cell popup menu.
    For Each cbc In Application.CommandBars("Cell").Controls
        If cbc.Caption = "Run My Macro" Then cbc.Delete
    Next cbc
 
End Sub

In a module:

Sub MyMacro()
 
    MsgBox "You ran my macro!"
 
End Sub

[big_k105]

ok now how to i make it run that code when i right click on a cell. sorry im pretty new to this programming Excell thing.

[WorkHorse]

Since you marked the topic resolved, I assume you figured out that you don't have to do anything. The custom control is added to the bottom of the normal drop down and Excel does the rest.

If you want only your wn custom drop down when the user right clicks you could do something like ths:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
 
    On Error Resume Next
 
    ' Delet the the Custom popup if it exists.
    Application.CommandBars("Custom").Delete
 
    ' Add the new custom popup.
    With Application.CommandBars.Add("Custom", msoBarPopup)
        With .Controls.Add
            .FaceId = 346               ' Set display picture.
            .Caption = "Run My Macro"   ' Set display text.
            .OnAction = "MyMacro"       ' Set macro to run.
        End With
        .ShowPopup
    End With
 
    Cancel = True
 
End Sub

Note that this applies to ANY right click, even if it is on a column or row.