Customizing the Excel Right Click Menu Still Works

xenou

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Hello Excel Enthusiasts,

In order to work with Excel more efficiently I use a variety of time-saving customizations: adding items to the Quick Access Toolbar (QAT), using keyboard shortcuts, creating custom keyboard shortcuts, and applying menu customizations.

This article is to highlight *one* of these customization techniques: customizing the "Cell" right click menu.

It is a two-step process. First, I remove items from the cell menu that I don't need. Second, I add custom menu items to suit my preferences. The result is that I have at my disposal exactly the menu items that make my work easier. An added advantage is that the cell menu it works the same in Excel 2003 and Excel 2007/2010, so it's a great way to add menu customizations in Excel 2003 that will continue to work the same when you move to Excel 2007 or Excel 2010.

So, let's take a look at the right-click menu in Excel 2007/2010. First we'll set it to it's default appearance:
Code: 
[COLOR="Navy"]Sub[/COLOR] RestoreMyCellMenu()
    Application.CommandBars("Cell").Reset
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

<img alt="image of default cell menu" src="http://northernocean.net/etc/mrexcel/rmenu_1.png" />

The menu isn't bad but the truth is I don't use all these items (some of them I *never* use and others I prefer to access with keyboard shortcuts). So we'll first remove a few things (and we "re-arrange" just a tad too - please don't ask me why I dim'med my array at 20. No idea really):
Code: 
[COLOR="Navy"]Sub[/COLOR] RMenus_Remove()

[COLOR="Navy"]Dim[/COLOR] myCommandBar [COLOR="Navy"]As[/COLOR] Office.CommandBar
[COLOR="Navy"]Dim[/COLOR] ctrl [COLOR="Navy"]As[/COLOR] Office.CommandBarControl
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] Integer, j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a(20) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
               
    [COLOR="SeaGreen"]'//A list of right click menu items I don't need to see[/COLOR]
    a(0) = "Pic&k From Drop-down List..."
    a(1) = "Add &Watch"
    a(2) = "&Create List..."
    a(3) = "&Hyperlink..."
    a(4) = "&Look Up..."
    a(5) = "Cu&t"
    a(6) = "Clear Co&ntents"
    a(7) = "&Format Cells..."
    a(8) = "S&ort"
    a(9) = "Filt&er"
    a(10) = "Name a &Range..."
    a(11) = "&Insert..."
    a(12) = "&Delete..."

    [COLOR="SeaGreen"]'//Tag the above list of inbuilt controls with my tag -[/COLOR]
    [COLOR="SeaGreen"]'  my remove procedure will then remove them just like my custom controls[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] myCommandBar [COLOR="Navy"]In[/COLOR] Application.CommandBars
        [COLOR="Navy"]If[/COLOR] myCommandBar.Name = "Cell" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] ctrl [COLOR="Navy"]In[/COLOR] myCommandBar.Controls
                [COLOR="Navy"]For[/COLOR] j = 0 [COLOR="Navy"]To[/COLOR] UBound(a)
                    [COLOR="Navy"]If[/COLOR] ctrl.Caption = a(j) [COLOR="Navy"]Then[/COLOR]
                        ctrl.Tag = MY_TAG
                        [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]For[/COLOR]
                    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
                [COLOR="Navy"]Next[/COLOR] j
            [COLOR="Navy"]Next[/COLOR] ctrl
         [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Next[/COLOR] myCommandBar

    [COLOR="SeaGreen"]'//Removed all tagged controls (whether custom or tagged inbuilt controls)[/COLOR]
    [COLOR="Navy"]Set[/COLOR] ctrl = Application.CommandBars.FindControl(Tag:=MY_TAG)
    [COLOR="Navy"]Do[/COLOR] Until ctrl [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR]
        ctrl.Delete
        [COLOR="Navy"]Set[/COLOR] ctrl = Application.CommandBars.FindControl(Tag:=MY_TAG)
    [COLOR="Navy"]Loop[/COLOR]

    [COLOR="SeaGreen"]'make some modifications to cell right click control (to improve appearance of menu items)[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]With[/COLOR] Application.CommandBars("Cell")
        .Controls("&Delete...").Move Before:=5
        .Controls("&Delete...").BeginGroup = False
        .Controls("Insert Com&ment").BeginGroup = False
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] 0

The result is a very simple right click menu, with only the things that I really like to use from this menu:

<img alt="image of cell menu" src="http://northernocean.net/etc/mrexcel/rmenu_2.png" />

Now I want to add some custom menu items that I'd like to have on the right click menu. These custom procedures created with VBA can be as simple as adding your company name to a cell, or as complex as launching a full-blown addin for monitoring the activity on your shop floor. The sample code here is a mix of some "real" procedures I've created and some placeholders. I began using this code years ago, and the original link I followed is referenced in the code (I made sure to include a sub menu for purposes of demonstration - if you start adding enough items, sub menus will help keep things organized):

Code: 
[COLOR="Navy"]Sub[/COLOR] RMenus_Add()

[COLOR="Navy"]Dim[/COLOR] cntrl [COLOR="Navy"]As[/COLOR] CommandBarControl
[COLOR="Navy"]Dim[/COLOR] SubMenuItem1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
[COLOR="SeaGreen"]'http://groups.google.com/groups?threadm=uiqh89AxAHA.1620%40tkmsftngp05[/COLOR]

    [COLOR="SeaGreen"]'//Remove custom controls (prevents adding them twice by mistake)[/COLOR]
    [COLOR="Navy"]Call[/COLOR] RMenus_Remove
     
    [COLOR="SeaGreen"]'//Add custom controls[/COLOR]
    [COLOR="Navy"]With[/COLOR] Application.CommandBars("Cell").Controls

        [COLOR="SeaGreen"]'-----------------------------------------------------[/COLOR]
        [COLOR="SeaGreen"]'//Submenu 1[/COLOR]
        [COLOR="Navy"]Set[/COLOR] SubMenuItem1 = .Add(Type:=msoControlPopup, ID:=1)
        [COLOR="Navy"]With[/COLOR] SubMenuItem1
            .Caption = "Cell FX"
            .OnAction = ""
            .Tag = MY_TAG
            .BeginGroup = False
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
        [COLOR="SeaGreen"]'//Items on submenu 1[/COLOR]
        [COLOR="Navy"]With[/COLOR] SubMenuItem1.Controls.Add
            .Caption = "~()"
            .OnAction = ThisWorkbook.Name & "!ReverseSign"
            .Tag = MY_TAG
            .BeginGroup = False
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
        [COLOR="Navy"]With[/COLOR] SubMenuItem1.Controls.Add
            .Caption = "+0"
            .OnAction = ThisWorkbook.Name & "!CoerceToNumber"
            .Tag = MY_TAG
            .BeginGroup = False
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
        [COLOR="Navy"]With[/COLOR] SubMenuItem1.Controls.Add
            .Caption = "/100"
            .OnAction = ThisWorkbook.Name & "!DivideBy100"
            .Tag = MY_TAG
            .BeginGroup = False
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
        [COLOR="Navy"]With[/COLOR] SubMenuItem1.Controls.Add
            .Caption = "Round"
            .OnAction = ThisWorkbook.Name & "!Round_Selection"
            .Tag = MY_TAG
            .BeginGroup = False
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
        [COLOR="SeaGreen"]'End Submenu 1[/COLOR]
        [COLOR="SeaGreen"]'-----------------------------------------------------[/COLOR]
        [COLOR="Navy"]With[/COLOR] .Add
            .Caption = "Format TBR"
            .OnAction = ThisWorkbook.Name & "!Macro1"
            .Tag = MY_TAG
            .BeginGroup = True
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
        [COLOR="Navy"]With[/COLOR] .Add
            .Caption = "Break Links"
            .OnAction = ThisWorkbook.Name & "!Macro2"
            .Tag = MY_TAG
            .BeginGroup = False
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
        [COLOR="Navy"]With[/COLOR] .Add
            .Caption = "View Daily Sales"
            .OnAction = ThisWorkbook.Name & "!Macro3"
            .Tag = MY_TAG
            .BeginGroup = False
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
        [COLOR="Navy"]With[/COLOR] .Add
            .Caption = "End Of Day Reports"
            .OnAction = ThisWorkbook.Name & "!Macro4"
            .Tag = MY_TAG
            .BeginGroup = True
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]

Now we have an uncluttered and useful cell menu that has just what I need and want:

<img alt="image of cell menu" src="http://northernocean.net/etc/mrexcel/rmenu_3.png" />

You would of course need the code to run the macros - drop these in too, in the same module as the menu code (some of these are "real" macros, others are just placeholders):
Code: 
[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] DivideBy100()
[COLOR="Navy"]Dim[/COLOR] r [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] Range

    Fast_XL True
    
    [COLOR="Navy"]Set[/COLOR] r = Selection
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] c [COLOR="Navy"]In[/COLOR] r
        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] Len(c.Value) = 0 [COLOR="Navy"]Then[/COLOR]
            c.Value = c.Value / 100
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Next[/COLOR] c
    
    Fast_XL False

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] ReverseSign()
[COLOR="Navy"]Dim[/COLOR] r [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] blnFlag [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]

    
    Fast_XL True
    
    [COLOR="Navy"]Set[/COLOR] r = Selection
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] c [COLOR="Navy"]In[/COLOR] r
        [COLOR="Navy"]If[/COLOR] c.HasFormula [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] IsNumeric(c.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] Left(c.Formula, 3) = "=-(" [COLOR="Navy"]And[/COLOR] Right(c.Formula, 1) = ")" [COLOR="Navy"]Then[/COLOR]
                    c.Formula = "=" & Mid(c.Formula, 4, Len(c.Formula) - 4)
                [COLOR="Navy"]Else[/COLOR]
                    c.Formula = Replace(c.Formula, "=", "=-(", 1, 1) & ")"
                [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]If[/COLOR] IsNumeric(c.Value) [COLOR="Navy"]Then[/COLOR]
                c.Value = -c.Value
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Next[/COLOR] c
    
    Fast_XL False

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] CoerceToNumber()
[COLOR="Navy"]Dim[/COLOR] r [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] blnFlag [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]
    
    Fast_XL True
    
    [COLOR="Navy"]Set[/COLOR] r = Selection
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] c [COLOR="Navy"]In[/COLOR] r
        [COLOR="Navy"]If[/COLOR] c.HasFormula [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] IsNumeric(c.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] Left(c.Formula, 2) = "=(" [COLOR="Navy"]And[/COLOR] Right(c.Formula, 3) = ")+0" [COLOR="Navy"]Then[/COLOR]
                    c.Formula = "=" & Mid(c.Formula, 3, Len(c.Formula) - 5)
                [COLOR="Navy"]Else[/COLOR]
                    c.Formula = Replace(c.Formula, "=", "=(", 1, 1) & ")+0"
                [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]If[/COLOR] IsNumeric(c.Value) [COLOR="Navy"]Then[/COLOR]
                c.Value = (c.Value) + 0
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Next[/COLOR] c
    
    Fast_XL False
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] Round_Selection()
[COLOR="Navy"]Dim[/COLOR] r [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] blnFlag [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]
[COLOR="Navy"]Dim[/COLOR] rsp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] roundTo [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

    [COLOR="Navy"]Set[/COLOR] r = Selection

    rsp = InputBox(Prompt:="Round To:", Default:=2)
        
    [COLOR="SeaGreen"]'//Validate round to input is between -10 and 10[/COLOR]
    [COLOR="Navy"]If[/COLOR] IsNumeric(rsp) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] CLng(rsp) > -11 [COLOR="Navy"]And[/COLOR] CLng(rsp) < 11 [COLOR="Navy"]Then[/COLOR]
            roundTo = CLng(rsp)
            
            [COLOR="SeaGreen"]'//Round formulas and/or values[/COLOR]
            Fast_XL True
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] c [COLOR="Navy"]In[/COLOR] r
                [COLOR="Navy"]If[/COLOR] c.HasFormula [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]If[/COLOR] IsNumeric(c.Value) [COLOR="Navy"]Then[/COLOR]
                        c.Formula = Replace(c.Formula, "=", "=Round(", 1, 1) & "," & roundTo & ")"
                    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
                [COLOR="Navy"]Else[/COLOR]
                    [COLOR="Navy"]If[/COLOR] IsNumeric(c.Value) [COLOR="Navy"]Then[/COLOR]
                        c.Value = Round(c.Value, roundTo)
                    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
                [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            [COLOR="Navy"]Next[/COLOR] c
            Fast_XL False
        
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] Fast_XL(ByRef FastXlOn [COLOR="Navy"]As[/COLOR] Boolean)
[COLOR="Navy"]Static[/COLOR] myCalc [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]

    [COLOR="Navy"]If[/COLOR] FastXlOn [COLOR="Navy"]Then[/COLOR]
        myCalc = Application.Calculation
        Application.Calculation = xlCalculationManual
    [COLOR="Navy"]Else[/COLOR]
        Application.Calculation = myCalc
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Sub[/COLOR] Macro1()
    MsgBox "Running macro Format TBR ..."
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Sub[/COLOR] Macro2()
    MsgBox "Running macro Break Links ..."
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Sub[/COLOR] Macro3()
    MsgBox "Running macro View Daily Sales ..."
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Sub[/COLOR] Macro4()
    MsgBox "Running macro End Of Day Reports ..."
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


Further Information and Gotchas:

1) There's some different strategies for adding/removing menus in Excel. Many times you will see that code is added to the Workbook_Open and Workbook_Close events. This works fine, for example, if the menu code was in your personal macro workbook - it would add the menu items when you start up Excel, and remove them when you close Excel. Since these menus are essentially permanent changes that I want to make to my Excel application, I just run the code once right from the Visual Basic editor when I want to add the menus. If I have modified the code to add something new, I just run it again - once - and it's done.

2) I keep the menu code and related subroutines in a module in my personal macro workbook, in an Excel startup folder, so that the procedures are available whenever Excel is open.

3) There is actually more than one "Cell" menu in Excel -- one is the right click cell menu I have shown, but there is a second one that appears when you are in print preview. In print preview, you will probably find that all of your custom menu items disappear (and some new inbuilt controls show up). If desired, you can customize *both* cell menus - I think I would do so by putting the code in a loop and executing it on all the "Cell" menus that are found in the command bars collection.

4) It may seem rather obscure knowing all these things about command bars. Usually, though, I just run a loop through the command bars collection to spit out the captions, id's, or whatever it is that I am interested in. Once you get used to creating such "discovery" routines, and using the object browser and the watch window, you can learn a whole lot right inside the visual basic editor window.

5) Though I've focused on add custom menu items, you can also add inbuilt menu items to your right click menu - this requires knowing the type and ID of the button. Here's an example for adding the "Save" button:
Code: 
[COLOR="Navy"]Sub[/COLOR] AddSaveButtonToCellMenu()
    [COLOR="Navy"]Call[/COLOR] RemoveSaveButtonFromCellMenu
    Application.CommandBars("Cell").Controls.Add _
        Type:=msoControlButton, ID:=3, before:=1
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Sub[/COLOR] RemoveSaveButtonFromCellMenu()
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    Application.CommandBars("Cell").FindControl(ID:=3).Delete
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] 0
End Sub

Result of running the AddSaveButtonToCellMenu procedure:

<img alt="image of cell menu" src="http://northernocean.net/etc/mrexcel/rmenu_5.png" />

Additional information:
A thread I was involved in that covered a lot of basics with some further excellent input:
http://www.mrexcel.com/forum/showthread.php?t=475070

Happy Excelling,

ξ
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
I have tried to implement solution as above - In private (Excel 2007), I add all proposed in Standard Modules.
It works: add Menus and restore my old menu, but Remove part - Sub RMenus_Remove do not work since I got error:
"Run-time error ´-2147467259 (80004005)´: Method ´Delete´ of object ´CommandBarControl´ failed" and error is in part of Sub RMenus_Remove part "ctrl.Delete".
I have been searching web for this error, but I failed. Some proposing solution was to get some appropiate Microsoft object library. I have microsoft office 12.0 object library and microsoft excel 12.0 object library.

Any tips, solutions since this is great efficiency tool.
thx
 
Upvote 0
Hi,
I've had similar errors which honestly don't make sense to me so I've gotten in the habit of using a workaround. First, to be sure to use an On Error Statement so that I don't get crashes, which helps:

Code: 
[COLOR="Navy"]Sub[/COLOR] Foo()
[COLOR="Navy"]Dim[/COLOR] cntrl [COLOR="Navy"]As[/COLOR] CommandBarControl
    
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    
    [COLOR="SeaGreen"]'//Find first tagged control[/COLOR]
    [COLOR="Navy"]Set[/COLOR] cntrl = Application.CommandBars.FindControl(Tag:=MTD_TAG)
    
    [COLOR="SeaGreen"]'//Delete tagged controls until no more are left[/COLOR]
    [COLOR="Navy"]Do[/COLOR] Until cntrl [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR]
        cntrl.Delete
        [COLOR="Navy"]Set[/COLOR] cntrl = Application.CommandBars.FindControl(Tag:=MTD_TAG)
    [COLOR="Navy"]Loop[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

But even here I've occasionally gotten trapped in an endless loop so I've also put in a counter variable as a stopgap against those weird days where the code seems to not exit the loop properly:

Code: 
[COLOR="Navy"]Sub[/COLOR] Bar()
[COLOR="Navy"]Dim[/COLOR] cntrl [COLOR="Navy"]As[/COLOR] CommandBarControl
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    
    [COLOR="SeaGreen"]'//Find first tagged control[/COLOR]
    [COLOR="Navy"]Set[/COLOR] cntrl = Application.CommandBars.FindControl(Tag:=MTD_TAG)
    
    [COLOR="SeaGreen"]'//Delete tagged controls until no more are left[/COLOR]
    [COLOR="Navy"]Do[/COLOR] Until cntrl [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Or[/COLOR] i > 100
        cntrl.Delete
        i = i + 1
        [COLOR="Navy"]Set[/COLOR] cntrl = Application.CommandBars.FindControl(Tag:=MTD_TAG)
    [COLOR="Navy"]Loop[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

This is a bit inelegant but it's good enough for me. Some of my workbooks use the original code I posted, but I if I get that error cropping up I'll just edit to the workaround code shown above and get on to bigger and better things.
 
Upvote 0
I tried your proposed solution, but as I see it do nothing, or I have no idea how it should do.
Meanwhile, I think I figure out how to delete specific Item, it is something like this:
Application.CommandBars("cell").Controls("Sort").Delete
for deleting Sort, other way should be knowing ID for that Item

Now, I am satisfied with partly solution from xenou, and other part I figure out by myself
thx again for replying
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Custom Context Menu not working for one User?
Replies
2
Views
491
jmpatrick
J
F
  • Question
Custom Right-click cell menu
Replies
4
Views
259
Formula11
F
K
  • Question
Right Click Context menu
Replies
0
Views
694
kgkev
K
F
  • Solved
Context menu - from Addin
Replies
2
Views
534
Formula11
F
W
  • Question
Custom functions on Excel Right-click menu (not working)
Replies
0
Views
136
wheelsguy
W

Forum statistics

Threads
1,215,212
Messages
6,123,654
Members
449,113
Latest member
Hochanz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top