This post describes how to add a new custom-built item to the shortcut menu in Excel, when you press with right mouse button on 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-press with left mouse button oning 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

1. Press Alt-F11 to open visual basic editor
2. Press with left mouse button on Module on the Insert menu
3. Copy and paste vba code.
4. Exit visual basic editor

Get the Excel file

Select-A1.xlsm

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, […]

How to save custom functions and macros to an Add-In

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

Auto refresh a pivot table

In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]

Filter unique distinct values, sorted and blanks removed from a range

EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]

Basic data entry [VBA]

In this small tutorial, I am going to show you how to create basic data entry with a small amount […]

How to use the TRANSPOSE function

The TRANSPOSE function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]

Highlight cells based on coordinates

The picture above shows Conditional Formatting highlighting cells in cell range F3:Y22 based on row and column values in column B […]

Normalize data [VBA]

To be able to use a Pivot Table the source data you have must be arranged in way that a […]