Customizing Context Menus with VBA in every Excel version
Introduction Context Menus
I can't insert MSCOMM in Controls (VBA). I already register MSCOMM32.OCX in cmd and it was successful. But seems my VBA can't recognized it. I researched a lot. But I can't find any solution.
- VBA uses the.Net runtime and its not possible to call outside of a browser. The only chance would be if microsoft ported the.Net runtime to webassembly (they have actually done this as a test). Then it would be possible for VBA to run in the browser. Wednesday, July 31, 2019 11:45 AM.
- VBA-Web VBA-Web: Connect VBA, Excel, Access, and Office for Windows and Mac to web services and the web Visual Basic MIT 402 1,494 101 18 Updated Feb 20, 2021.
- Your Mac must have a live internet connection to run queries on web pages that are found on the internet. There are three ways to run a web query in Excel 2011. Excel comes with some example web queries.
The most common Context Menu that most people know and use is the Cell menu.
This is the menu that you see when you right click on a worksheet cell or selection.
The screenshot below is from the Excel 2010 Cell menu.
Note: On the bottom of the menu you also see the name of the idMso of this
menu added by the Microsoft add-in discussed later in this article.
The only way to change this menu up to Excel 2007 is to use VBA code, but in Excel 2010 and up
you can also change a context menu with RibbonX. Visit this page for RibbonX examples :
Add button and menu to the Cell menu with VBA code(Every Excel version)
Microsoft Internet Controls Vba Macro
This example will add a custom button, built-in button (Save) and a Custom menu on top of the Cell menu. Other context menus that you can change are the Row and Column context menus for example.These are the menus that you see when you right click on the row or column headers. See the Tips section for a tip how to find the names of the other context menus.
Note: There are two Cell menu's in Excel, the second one you see when you are in page break preview mode. If you want to change this menu use this then in the code.
Set ContextMenu = Application.CommandBars(Application.CommandBars('Cell').Index + 3)
The same applies to the Row and Column context menus.
Copy the six macro's below into a General module of your workbook.
If you do not know where to copy the code check out this page.
Where do I paste the code that I find on the internet
The first macro adds the controls to the Cell menu (see how I add a Tag to the controls I add).
The second macro deletes the controls from the Cell menu (See how I use the Tag to delete the controls).
The other four macros will run when you click on the Button or on one of the three options in the menu.
As a example I use macro's that change the Case of the text cells in the selection.
Copy the two event procedures below in the Thisworkbook module of your workbook This will automatically add the controls to the Cell menu when you open or activate the workbook and delete the controls when you close or deactivate the workbook
Then save, close and reopen the workbook to see the changes in the Cell menu.
Tips for Excel 97-2013
Change the Activate event to run different macros to create different menus
In the VBA example on this page I showed you how to change the Cell menu and I used the Activate and Deactivate event of the workbook to call a macro to create and delete the menu controls.
You can change the Activate event like this to create different menus for different users, this way you can create a menu with different sets of custom controls for different users. If you use the same Tags in the code there is no need to call a different macro to delete the menu.
In the example above you will see no changes in the menu if you are not Ron de Bruin. See the two commented lines that show you how to call a different macro if another user opens the workbook.
Another example is to check for the Excel language in the activate event so you can create menu captions in the language of the user in the context menu. In this case if a Dutch or German user opens the workbook no menu will be created because I commented out the two lines that call the macros you could make for these two languages. For all other languages in this example the macro AddToCellMenu is called and it creates a menu with English captions
For a list of country codes see
How do I find the name of the contextmenu that I want to change ?
The example macro below will add button on the bottom of each contextmenu with the menu name.
You will notice that you will not see a name on each context menu when you run it in Excel 2007-2013.
For example, you will not see it when you right click on a shape or Picture in Excel 2007-2010.
So it not seems to be possible to change these menus in Excel 2007-2010 with VBA.
Microsoft Internet Controls Vba Macros
How do I find the control ID's or FaceID's :
Download Excel file with all FaceID's from my site, you can use this file in Excel 97-2013
Find the ID's and FaceId's see this page from Ole P. Erlandsen,