Add item to right click menu?

[davez]

Hi all, I use Excel 365 & often have to paste large amounts of data from an external source as text, which involves an extra couple of clicks through the right click menu - paste special/text/ok. So to save myself some time I am wondering if theres is a way to add the paste text option to the right click menu?

 

[Micron]

Altering context menus is tricky. Can you not use a macro to perform the steps?

 

[johnnyL]

It is actually fairly easy to add an entry to the right-click context menu in Excel.

example of code to copy/paste into the 'ThisWorkbook' module of a workbook:

Option Explicit

Private Sub Workbook_Open()
'
    Dim cmdBtn  As CommandBarButton
'
    Set cmdBtn = Application.CommandBars("Cell").Controls.Add(Temporary:=True, Before:=1, Type:=msoControlButton)   ' Add a custom button option to the right-click context menu.
'
    With cmdBtn
        .Caption = "Paste Special Text"                                                                             '   Caption for the added right click menu button
        .FaceId = 755                                                                                               '   Icon to display for the added right click menu button
        .OnAction = "PasteSpecialText"                                                                              '   Name of the code module that you want to run when the added right click option is selected
    End With
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
    Application.CommandBars("Cell").Controls("Paste Special Text").Delete                                           ' Delete the custom right click button when the workbook is closed.
End Sub

That /\ /\ /\ code will add the 'Paste Special Text' option to the top of the right click menu


And then in a regular module, an example of the code that you want to run when the added right click option is selected:

Sub PasteSpecialText()
'
    ActiveSheet.PasteSpecial Format:="Text"
End Sub

 

[davez]

Many thanks, works a charm!

 

[johnnyL]

Glad to help.