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:
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:
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
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
Turning Off Paste Options
Can the administrators not automatically re-submit a question - as if it had just been initially posted ?
@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.
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.
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
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.
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
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
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.
I can't install addins in a client environment and I can't add any addin's to my system.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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.
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.
:-)
I am glad it worked.
here is the previous thread about ribbonX. https://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.
I am glad it worked.
here is the previous thread about ribbonX. https://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.
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]()
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
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.
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.
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.
if time does not allow then, i would prefer using the VBA solution.
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>
if time does not allow then, i would prefer using the VBA solution.
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
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
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.