Excel VBA: Clear the 'Cell' context menu and all items

Requirement:

I want to clear all items on the Cell context menu using VBA.

Objective:

I am adding my own 'paste command' to the cell menu and want to remove all other commands and means of paste via the cell menu.

Context:

I have used Ribbon X to add a new custom item to the cell menu and I want to clear all other items - ICLUDING the 'Paste Options' sub-menu as below:

Excel-Paste-Options-sub-menu.png
Current progress:

I have previously asked on EE and found that there is no RibbonX way (PREFERRED) to remove all items on the context menu  (a very big omission on the part of MS) and that the only remaining option is to use the now depreciated 'CommandMenu' control objects to remove all builtin items. This is done as follows:

Sub removeCellMenuItems()

    Dim cbcCurControl As CommandBarControl
    
    For Each cbcCurControl In CommandBars("Cell").Controls
    
        If cbcCurControl.BuiltIn Then cbcCurControl.Visible = False
    
    Next cbcCurControl

End Sub

Open in new window

This of course works but leaves behind the annoying 'Paste Options' submenu. How can I get rid of this ?
LVL 3
AL_XResearchAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AL_XResearchAuthor Commented:
Please note I have found articles such as the below but despite turning off all three 'Cut, copy & paste' options (and restarting Excel - just in case) this 'Paste Options' floating gallery is still there !

Turning Off Paste Options
AL_XResearchAuthor Commented:
Does no expert in the world know any way to do this ??
AL_XResearchAuthor Commented:
Can the administrators not automatically re-submit a question - as if it had just been initially posted ?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
ProfessorJimJamMicrosoft Excel ExpertCommented:
@AL_XResearch


you can get what you want using VBA, hardway will be to run the below code and get list of all shortcut menus and then incorporate it into the VBA code.

Sub ListShortCutMenus()
    Dim r As Long
    Dim cBar As CommandBar
    Dim c As Integer
    r = 1
    For Each cBar In CommandBars
        If cBar.Type = msoBarTypePopup Then
            Cells(r, 1) = cBar.Index
            Cells(r, 2) = cBar.Name
            For c = 1 To cBar.Controls.Count
                Cells(r, c + 2) = cBar.Controls(c).Caption
            Next c
            r = r + 1
        End If
    Next cBar
End Sub

Open in new window



easy way will be using the free Add-in  once loaded the add-in then it will appear in the developer tab then click on the Menurighter add-in icon and then once it is open,  on the "target" drop down there are to "cell"  select the first one, the from the left icon "remove" or by double click remove all of the menus and leave only the last one which is "&Additional Actions"  then from the dropdown of "target" select the second "cell"  and do the same thing remove all and leave only "&Additional Actions" then close add-in.  you will see that your right click will have nothing, not even the paste special gallery or menu.

let me know how it goes.
AL_XResearchAuthor Commented:
ProfessorJimJam: Yes I have already tried this but as I say it does not remove 'Paste options' indeed that 'gallary' style control is not even listed when you output all control captions for the "Cell" menu to a worksheet.

Clearing all controls in the "Cell" menu does not clear the "Paste options" control.

All this is shown in the picture below



This still leaves me looking for a solution
ProfessorJimJamMicrosoft Excel ExpertCommented:
can you try like this?   it does remove all including the gallery of paste .
AL_XResearchAuthor Commented:
ProfessorJimJam: Yes that looks exactly what I want. However I need the RibbonX or VBA code (preferably RibbonX).

I can't install addins in a client environment and I can't add any addin's to my system.
ProfessorJimJamMicrosoft Excel ExpertCommented:
ok, you mentioned Ribbon X or VBA code.

if i recall correctly. in another question, i provided some detail on the ribbonX method.

well, here with the VBA  use the below code to remove all the contexual cell menu and 
Sub Delmenu()
Set CtrlMenu = Application.CommandBars("Cell")
For Each Item In CtrlMenu.Controls
Item.Delete
Next
End Sub

Open in new window


to reset back to defaul then use the below code

Sub ResetMenu()
    Application.CommandBars("Cell").Reset
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AL_XResearchAuthor Commented:
ProfessorJimJam: That is very interesting. My first though when I saw your latest example was "that won't work - I have tried that before and it left the 'Paste Options' control behind" but it did work.

It appears that if you set each control to ".visible = False" then 'Paste Options' remain but if you 'Delete' them it clears all.

You mentioned you have already given the RibbonX for this in another post but I don't think that was me as I have been asking this on EE in various forms for some time now and nobody has yet solved it (apart from yourself now :) )

I would really appreciate you given the RibbonX if there is any. Cheers.
ProfessorJimJamMicrosoft Excel ExpertCommented:
:-)

I am glad it worked.

here is the previous thread about ribbonX. http://www.experts-exchange.com/questions/28674307/Excel-ribbon-customisation-contextmenus.html

but if the VBA command works for you, i suggest, you use it. as it is much easier to manipulate and reset.
AL_XResearchAuthor Commented:
Unfortunately when I tried the RibbonX that you supplied (ID: 40785604) it did not remove all the items in the conext menu as shown below.

Remaining options after RibbonX applied to workbook
ProfessorJimJamMicrosoft Excel ExpertCommented:
is this case, then please use the VBA solution on this thread.
AL_XResearchAuthor Commented:
I want to keep away from the CommandBar objects since a) I don't know when they will be depreciated by MS b) I don't want to mix interface specification between RibbonX and VBA
AL_XResearchAuthor Commented:
So you are saying there is no RibbonX way to do what I want as far as you know ?

I would also point out that with RibbonX the user interface adjustments are automatically revoked & restored when you switch workbooks / sessions whereas with VBA you have to be careful to implement this yourself.
ProfessorJimJamMicrosoft Excel ExpertCommented:
there is possibility to do this, but it require a bit of time to manipulate. for example apart from the commands idMSOs



you need to find the contextual menus for the paste menus to disable.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <contextMenus>
        <contextMenu idMso="ContextMenuCell">
            <gallery idMso="PasteGalleryMini" visible="false"/>
        </contextMenu>
    </contextMenus>
</customUI>

Open in new window


if time does not allow then, i would prefer using the VBA solution.
AL_XResearchAuthor Commented:
I have not so far found the IdMSO values and MS does not appear to document them
ProfessorJimJamMicrosoft Excel ExpertCommented:
i dont know which version excel you are using.

it appears that the leftover paste is gallery type control

if you have excel 2010 then You can refer to  http://www.microsoft.com/en-us/download/details.aspx?id=6627 Office 2010 Help Files: Office Fluent User Interface Control Identifiers to download the control ID list  

you need to write like  <gallery idMso="name of control iD"  to disable.

there is tons of example and useful material in Ron's website for RibbonX control.

http://www.rondebruin.nl/win/s2/win003.htm

http://www.rondebruin.nl/win/s2/win014.htm
AL_XResearchAuthor Commented:
Not ideal but it works
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.