Right Click Menu - Adding (Paste Special)

[Tirren]

I want to add Paste Values to the right click menu in Excel 2007. Can this be done without recording a separate macro to run it? What I mean is If I add the command itself to the right click menu, can it run of of the standard RightClick -> Paste Special -> Values option, or must I record a macro that does those steps each time?

[JBeaucaire]

Creating a macro and assigning a permanent keyboard shortcut is much less complicated than what you're asking. And in the end activating a keyboard shortcut is the same number of steps as right-click-choose...so, macro gets my vote.

[Tirren]

Well you can add Paste Values (no macro required) directly to the QAT, in fact you can do any of the paste special functions via the QAT no macro required. I assumed it might be just as easy to add them to the Right Click menu, I just have no clue how to do it. Seems pointless to write another macro, to perform the exact same task.

[Leith Ross]

Hello Tirren,

I don't have 2007, but it seems reasonable. I have modified the cell context menu many times in Excel 2003 to provide users with faster data entry.

Sincerely,
Leith Ross

[Tirren]

Hello Tirren,

I don't have 2007, but it seems reasonable. I have modified the cell context menu many times in Excel 2003 to provide users with faster data entry.

Sincerely,
Leith Ross

In 2003, what method did you do for adding an excel pre-programmed feature to the menu. The only method I know currently is recording a separate macro to accomplish the same feat, then adding that macro to the right click menu. Problem is, you can't undo actions done by a macro, so if I make a mistake I cannot easily fix it.

If I use the paste special function already designed in Excel, then I should be good. I just don't know how to contextually add it to the right click menu. Here is the code that I have to add a pre-programmed macro to the right click... perhaps you can tell me what to edit, to point to the excel function already available:

Private Sub Workbook_Open()
    Application.CommandBars("Cell").Reset
    With Application.CommandBars("Cell").Controls
        With .Add
            .Caption = "Paste Values"
            .OnAction = ThisWorkbook.Name & "!PasteSpecial"
            .Tag = cControlTag
            .BeginGroup = True
        End With
    End With
End Sub

[lecxe]

Hi Tirren

Try:

Application.CommandBars("Cell").Controls.Add Type:=msoControlButton, ID:= 370, Before:=5

[Tirren]

Hi Tirren

Try:

Application.CommandBars("Cell").Controls.Add Type:=msoControlButton, ID:= 370, Before:=5

AWESOME! You got it! You also answered a question I had in another post, not sure if you had intended to.

Might I ask where you find those ID's?

[lecxe]

I'm glad it helped.

I don't have a full list of the ids for excel 2007. You find here a list of the ids for excel 2000. I'm not sure they all work in xl2007 and it doesn't have the paste values. If you find a more recent list please post it.


http://support.microsoft.com/kb/213552/en-us

[fablepd]

Thank you very much Tirren !! You saved me !!!

[fablepd]

lecxe i cannot find ID 370 in the support.microsoft.com you gave us ;(
i'm looking for the "paste as Unicode text" ID. Can you help me please ?
Thank you very much.

Fabio