How to change excel's right click menu to Insert Cells DOWN, instead of ToRight using VBA

CrunchMasterMatt

Board Regular
Joined
Jul 20, 2012
Messages
123
I've upgraded to "Excel 2019 Home and Student" and can't find a way to change the default right click menu option to "Insert Cells: DOWN" versus the current default selection of inserting cells to the right? So in other words, every time I want to insert cells, I have to change the direction to DOWN from ToRight.

I can't seem to find an Event handler to address this and it doesn't appear to be in the default user settings?

It's been 8 years since I've coded inside excel (using excel 2013) so I am a bit rusty. That said, I've not coded any customized "right click menu items" other than to outright cancel the right click event itself but it seems maybe the solution would be to add a special "Insert Cells Down" right click event and add it to my right click menu? I'd like to put the final solution into all future workbooks I open by default, which as I recall you can save a workbook template to accomplish this. That will be my last step so I may have more Q's as I tackle this solution.

Thank you in advance for the help!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
One way.
In the workbook module:
VBA Code:
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim cmdBtn As CommandBarButton
    Dim MacroArr As Variant, CaptionArr As Variant, X As Variant
    Dim N As Long
    
    MacroArr = Array("GoRight", "GoDown")
    CaptionArr = Array("Insert Right", "Insert Down")
    
    On Error Resume Next
    For Each X In CaptionArr
        With Application
            .CommandBars("Cell").Controls(X).Delete
        End With
    Next X
    On Error GoTo 0

    N = 0
    For Each X In MacroArr
        With Application
            Set cmdBtn = .CommandBars("Cell").Controls.Add(Temporary:=True)
        End With
        With cmdBtn
            .Caption = CaptionArr(N)
            .Style = msoButtonCaption
            .OnAction = X
        End With
        N = N + 1
    Next X
End Sub

In a standard code module:
VBA Code:
Sub GoRight()
    Application.MoveAfterReturnDirection = xlToRight
    Application.StatusBar = "Insert to right"
End Sub

Sub GoDown()
     Application.MoveAfterReturnDirection = xlDown
     Application.StatusBar = "Insert down"
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top