troubleshooting Question

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

Avatar of AL_XResearch
AL_XResearchFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft OfficeVisual Basic ClassicMicrosoft ExcelOffice Suites-OtherMicrosoft Development
18 Comments1 Solution2059 ViewsLast Modified:
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
This of course works but leaves behind the annoying 'Paste Options' submenu. How can I get rid of this ?
ASKER CERTIFIED SOLUTION
Professor JSpreadsheets Expert
Join our community to see this answer!
Unlock 1 Answer and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros