Right Click a Cell

[Duncan Butterworth]

Hi,
When I right click on a cell I get a drop down menu and a box containing tools above or blow it, I would like to customize this box of tools and the drop down menu also, is this possible.
Thanking you in advance.
Regards,
Duncan.

 

[tyija1995]

Hey,

It is definitely possible, take a look at CommandBar, CommandBarButton, Application.CommandBars - i'm not too familiar with how to set it up but I know you can add macro's to the cell shortcut menu (right click menu) and implement them with open/close workbook events with add-ins etc, as I use one for indexing through the worksheets in the active workbook

Try this as a test:

Sub MessageBox()
    MsgBox Application.UserName
End Sub


Sub AddToCellShortcutMenu()
    Dim Bar As CommandBar
    Dim NewControl As CommandBarButton
    DeleteFromShortcut
    Set Bar = Application.CommandBars("Cell")
    Set NewControl = Bar.Controls.Add _
        (Type:=msoControlButton, ID:=2, _
        temporary:=True)
    With NewControl
        .Caption = "&My Message Box"
        .OnAction = "MessageBox"
        .Style = msoButtonIconAndCaption
    End With
End Sub


Sub DeleteFromShortcut()
    On Error Resume Next
    Application.CommandBars("Cell").Controls("&MessageBox").Delete
End Sub

 

[tyija1995]

Apologies - the last line on the DeleteFromShortcut sub should be "&My Message Box" instead of "&MessageBox" - it must match the caption from the AddToCellShortcutMenu procedure.