How to add a custom-made item to the shortcut menu [VBA]
This post describes how to add a new custom-built item to the shortcut menu in Excel, when you right-click a cell a context menu appears. Let's add a new item to the cell context shortcut menu and link it to a macro.
I demonstrated in a previous post how to automatically select cell A1 on every sheet in a workbook using vba. This is the macro we are going to use. This macro selects cell A1 in all sheets in the active workbook. Copy the following code to a regular code module, see details below.
Macro VBA code:
Sub SelectA1() Dim sht As Worksheet, csheet As Worksheet Application.ScreenUpdating = False Set csheet = ActiveSheet For Each sht In ActiveWorkbook.Worksheets If sht.Visible Then sht.Activate Range("A1").Select ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 End If Next sht csheet.Activate Application.ScreenUpdating = True End Sub
This vba code adds an item on the cell shortcut menu. Copy the code into the standard code module.
Sub AddItemShortCutMenu() Dim Shortcut As CommandBar Dim NewItem As CommandBarButton Set Shortcut = Application.CommandBars("Cell") Set NewItem = Shortcut.Controls.Add(Type:=msoControlButton) With NewItem .OnAction = "SelectA1" .Caption = "Select cell A1 in all sheets in this workbook" End With End Sub
This vba code removes the item on the cell shortcut menu. Copy the code into the standard code module.
Sub RemoveItemFromShortCutMenu() On Error Resume Next CommandBars("Cell").Controls("Select cell A1 in all sheets in this workbook").Delete End Sub
The code below adds the item on the shortcut menu whenever this workbook is opened. It also deletes the Item from the shortcut menu whenever the workbook is closed. Copy the code below and paste it into the ThisWorkbook code window. You open ThisWorkbook code window by double-clicking ThisWorkbook in the project explorer.
Private Sub Workbook_Open() Call AddItemShortCutMenu End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call RemoveItemFromShortCutMenu End Sub
Create an add-in
Now if you save the workbook as an add-in and install the add-in, the added item on the shortcut menu is always available.
How to create an add-in: Save your custom functions and macros in an Add-In
How to copy vba code into a standard code module
- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste vba code.
- Exit visual basic editor
Customize the ribbon and how to add your macros
Save links to your favorite macros in a personal tab on the ribbon for easy access and become more productive. […]
Customize the ribbon and how to add your macros
How to save custom functions and macros to an Add-In
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.