Refining my customised menu

[nigelandrewfoster]

Hi, just a quickie. Have customised the rightclick 'cell' menu so when I rightclick on a cell, the menu is displayed and I run a macro. The new menu item is displayed at the BOTTOM of the menu (after the preset commands). Is there a way of a) Deleting some of the preset commands above my new item that I don't use? b) Can I otherwisse move my new command further up the pecking order? Thanks. NIGEL

 

[John_w]

To remove cell right-click menu options, for example Cut, Copy and Paste:

    CommandBars("Cell").Controls("Cut").Delete
    CommandBars("Cell").Controls("Copy").Delete
    CommandBars("Cell").Controls("Paste").Delete

To add your custom menu option as the first item:

    With CommandBars("Cell").Controls.Add(msoControlPopup, Before:=1)
        .Caption = "My Menu Option"
    End With

 

[nigelandrewfoster]

Thanks for your help, John. It never ceases to amaze me just how customizable Excel is! Final question, I will need to RESTORE these fixed deleted options when the workbook closes, what command should I use? I know about events, but I can't specify a macro name for 'CUT' for example, because it is a built in command and not a macro. Nigel.

 

[John_w]

The easiest way is to reset the Cell right-click menu, which deletes the customised options and restores the default options:

Application.CommandBars("Cell").Reset

 

[nigelandrewfoster]

Thanks, John. Appreciate your help.