add "paste values" to right click menu

[rockhammer]

From the msword forum I see a reference to
http://www.word.mvps.org/FAQs/Customization/AsgnCmdOrMacroToToolbar.htm,
which shows a way to add an item to the right click menu or what seems to be
more properly called shortcut menu.

I am trying to do the same thing in excel but in Tools->Customize->Toolbars
there is not such thing as "shortcut menu" used by the above referenced
technique, or at least I have not found it. However, from
Tools->Customize->Commands (then choose "Edit" on the left hand pane), I was
able to find the actual "paste values" menu item and have successfully
dragged & dropped it to my File->Edit menu.

I did see the post in this forum on "PAStE SPECIAL" [sic] by Dave Peterson
on 2007/02/24 on how to assign a macro to a keystroke shortcut. However
actually adding that option menu item to the right click menu is preferred
since it: a) is a personal preference, and b) much more importantly, avoids
the inability to undo issue noted by another user in the "PAStE SPECIAL"
thread.

So my two questions:

1) How do you add the "paste values" menu item onto the right click menu (or
the shortcut menu, although that term might not be used within the excel
context)?

2) How do you add the "paste values" menu item onto ALL THE right click
menus since it appears that the right click menu changes depending on context.

Thanks a lot.

 

[Ron de Bruin]

Hi rockhammer

Try this

You can run the first macro one time to add it to the Cell menu for always

Sub Add_Paste_Special_Button()
' This will add the Paste Special Values button to the cell menu
' after the Paste option
Dim Num As Long
Num = Application.CommandBars("Cell"). _
FindControl(ID:=755).Index
Application.CommandBars("cell").Controls. _
Add Type:=msoControlButton, ID:=370, before:=Num
End Sub

Sub Delete_Paste_Special_Button()
On Error Resume Next
Application.CommandBars("cell").FindControl(ID:=370).Delete
On Error GoTo 0
End Sub

 

[rockhammer]

Hi Ron, thanks a lot for this suggestion. It works great!

If I may, I have a few followup questions as you might have already
anticipated...

1) I noticed that there is only the "before" option, not an "after" option
in the commandbars.controls.add method. So is there a simple way to put
"paste values" AFTER "paste special"?

2) In general, is there a way to figure out which ID corresponds to which
menu items? And how do you identify & position those separators in the right
click menu?

3) Your add_paste_special_button routine puts the keystroke "shortcut" on
the "p" in "paste values" as shown by the underline that appears under the
letter "p". Is there a way to modify the code to assign the shortcut to a
different letter? The original "paste" right click menu item already uses the
"p".

4) Over time I've come to realize several routines/techniques I found in
this forum, including the pair you suggested, to be very useful and I am
thinking of creating a personal add-in incorporating all of them so I can use
these codes on all spreadsheet I develop without having to repeat the code in
each spreadsheet. Is there a way to do that?

Thanks a lot.

Hi rockhammer

Try this

You can run the first macro one time to add it to the Cell menu for always

Sub Add_Paste_Special_Button()
' This will add the Paste Special Values button to the cell menu
' after the Paste option
Dim Num As Long
Num = Application.CommandBars("Cell"). _
FindControl(ID:=755).Index
Application.CommandBars("cell").Controls. _
Add Type:=msoControlButton, ID:=370, before:=Num
End Sub

Sub Delete_Paste_Special_Button()
On Error Resume Next
Application.CommandBars("cell").FindControl(ID:=370).Delete
On Error GoTo 0
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

From the msword forum I see a reference to
http://www.word.mvps.org/FAQs/Customization/AsgnCmdOrMacroToToolbar.htm,
which shows a way to add an item to the right click menu or what seems to be
more properly called shortcut menu.

I am trying to do the same thing in excel but in Tools->Customize->Toolbars
there is not such thing as "shortcut menu" used by the above referenced
technique, or at least I have not found it. However, from
Tools->Customize->Commands (then choose "Edit" on the left hand pane), I was
able to find the actual "paste values" menu item and have successfully
dragged & dropped it to my File->Edit menu.

I did see the post in this forum on "PAStE SPECIAL" [sic] by Dave Peterson
on 2007/02/24 on how to assign a macro to a keystroke shortcut. However
actually adding that option menu item to the right click menu is preferred
since it: a) is a personal preference, and b) much more importantly, avoids
the inability to undo issue noted by another user in the "PAStE SPECIAL"
thread.

So my two questions:

1) How do you add the "paste values" menu item onto the right click menu (or
the shortcut menu, although that term might not be used within the excel
context)?

2) How do you add the "paste values" menu item onto ALL THE right click
menus since it appears that the right click menu changes depending on context.

Thanks a lot.

 

[Ron de Bruin]

hi rockhammer

1) correct

755 = Paste special

We can change the macro to this ( num +1)

Sub Add_Paste_Special_Button()
Dim Num As Long
Num = Application.CommandBars("Cell"). _
FindControl(ID:=755).Index
Application.CommandBars("cell").Controls. _
Add Type:=msoControlButton, ID:=370, before:=Num + 1
End Sub


2) We use findcontrol to find the ID and then we also now the index

See this page for more info
http://www.rondebruin.nl/menuid.htm


3) We insert the build-in button
Add Type:=msoControlButton, ID:=370
We have no control over the shortcut
You can make your own macro if you want to change that

4)
See
http://www.rondebruin.nl/personal.htm


If you need more help post back




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Ron, thanks a lot for this suggestion. It works great!

If I may, I have a few followup questions as you might have already
anticipated...

1) I noticed that there is only the "before" option, not an "after" option
in the commandbars.controls.add method. So is there a simple way to put
"paste values" AFTER "paste special"?

2) In general, is there a way to figure out which ID corresponds to which
menu items? And how do you identify & position those separators in the right
click menu?

3) Your add_paste_special_button routine puts the keystroke "shortcut" on
the "p" in "paste values" as shown by the underline that appears under the
letter "p". Is there a way to modify the code to assign the shortcut to a
different letter? The original "paste" right click menu item already uses the
"p".

4) Over time I've come to realize several routines/techniques I found in
this forum, including the pair you suggested, to be very useful and I am
thinking of creating a personal add-in incorporating all of them so I can use
these codes on all spreadsheet I develop without having to repeat the code in
each spreadsheet. Is there a way to do that?

Thanks a lot.

Hi rockhammer

Try this

You can run the first macro one time to add it to the Cell menu for always

Sub Add_Paste_Special_Button()
' This will add the Paste Special Values button to the cell menu
' after the Paste option
Dim Num As Long
Num = Application.CommandBars("Cell"). _
FindControl(ID:=755).Index
Application.CommandBars("cell").Controls. _
Add Type:=msoControlButton, ID:=370, before:=Num
End Sub

Sub Delete_Paste_Special_Button()
On Error Resume Next
Application.CommandBars("cell").FindControl(ID:=370).Delete
On Error GoTo 0
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

From the msword forum I see a reference to
http://www.word.mvps.org/FAQs/Customization/AsgnCmdOrMacroToToolbar.htm,
which shows a way to add an item to the right click menu or what seems to be
more properly called shortcut menu.

I am trying to do the same thing in excel but in Tools->Customize->Toolbars
there is not such thing as "shortcut menu" used by the above referenced
technique, or at least I have not found it. However, from
Tools->Customize->Commands (then choose "Edit" on the left hand pane), I was
able to find the actual "paste values" menu item and have successfully
dragged & dropped it to my File->Edit menu.

I did see the post in this forum on "PAStE SPECIAL" [sic] by Dave Peterson
on 2007/02/24 on how to assign a macro to a keystroke shortcut. However
actually adding that option menu item to the right click menu is preferred
since it: a) is a personal preference, and b) much more importantly, avoids
the inability to undo issue noted by another user in the "PAStE SPECIAL"
thread.

So my two questions:

1) How do you add the "paste values" menu item onto the right click menu (or
the shortcut menu, although that term might not be used within the excel
context)?

2) How do you add the "paste values" menu item onto ALL THE right click
menus since it appears that the right click menu changes depending on context.

Thanks a lot.

 

[rockhammer]

Thanks for the amazingly fast responses! Your help is much appreicated.

hi rockhammer

1) correct

755 = Paste special

We can change the macro to this ( num +1)

Sub Add_Paste_Special_Button()
Dim Num As Long
Num = Application.CommandBars("Cell"). _
FindControl(ID:=755).Index
Application.CommandBars("cell").Controls. _
Add Type:=msoControlButton, ID:=370, before:=Num + 1
End Sub


2) We use findcontrol to find the ID and then we also now the index

See this page for more info
http://www.rondebruin.nl/menuid.htm


3) We insert the build-in button
Add Type:=msoControlButton, ID:=370
We have no control over the shortcut
You can make your own macro if you want to change that

4)
See
http://www.rondebruin.nl/personal.htm


If you need more help post back




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Ron, thanks a lot for this suggestion. It works great!

If I may, I have a few followup questions as you might have already
anticipated...

1) I noticed that there is only the "before" option, not an "after" option
in the commandbars.controls.add method. So is there a simple way to put
"paste values" AFTER "paste special"?

2) In general, is there a way to figure out which ID corresponds to which
menu items? And how do you identify & position those separators in the right
click menu?

3) Your add_paste_special_button routine puts the keystroke "shortcut" on
the "p" in "paste values" as shown by the underline that appears under the
letter "p". Is there a way to modify the code to assign the shortcut to a
different letter? The original "paste" right click menu item already uses the
"p".

4) Over time I've come to realize several routines/techniques I found in
this forum, including the pair you suggested, to be very useful and I am
thinking of creating a personal add-in incorporating all of them so I can use
these codes on all spreadsheet I develop without having to repeat the code in
each spreadsheet. Is there a way to do that?

Thanks a lot.

Hi rockhammer

Try this

You can run the first macro one time to add it to the Cell menu for always

Sub Add_Paste_Special_Button()
' This will add the Paste Special Values button to the cell menu
' after the Paste option
Dim Num As Long
Num = Application.CommandBars("Cell"). _
FindControl(ID:=755).Index
Application.CommandBars("cell").Controls. _
Add Type:=msoControlButton, ID:=370, before:=Num
End Sub

Sub Delete_Paste_Special_Button()
On Error Resume Next
Application.CommandBars("cell").FindControl(ID:=370).Delete
On Error GoTo 0
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


From the msword forum I see a reference to
http://www.word.mvps.org/FAQs/Customization/AsgnCmdOrMacroToToolbar.htm,
which shows a way to add an item to the right click menu or what seems to be
more properly called shortcut menu.

I am trying to do the same thing in excel but in Tools->Customize->Toolbars
there is not such thing as "shortcut menu" used by the above referenced
technique, or at least I have not found it. However, from
Tools->Customize->Commands (then choose "Edit" on the left hand pane), I was
able to find the actual "paste values" menu item and have successfully
dragged & dropped it to my File->Edit menu.

I did see the post in this forum on "PAStE SPECIAL" [sic] by Dave Peterson
on 2007/02/24 on how to assign a macro to a keystroke shortcut. However
actually adding that option menu item to the right click menu is preferred
since it: a) is a personal preference, and b) much more importantly, avoids
the inability to undo issue noted by another user in the "PAStE SPECIAL"
thread.

So my two questions:

1) How do you add the "paste values" menu item onto the right click menu (or
the shortcut menu, although that term might not be used within the excel
context)?

2) How do you add the "paste values" menu item onto ALL THE right click
menus since it appears that the right click menu changes depending on context.

Thanks a lot.

 

[Paul C.]

Ron, you've helped big time with this code - thanks.

Hi rockhammer

Try this

You can run the first macro one time to add it to the Cell menu for always

Sub Add_Paste_Special_Button()
' This will add the Paste Special Values button to the cell menu
' after the Paste option
Dim Num As Long
Num = Application.CommandBars("Cell"). _
FindControl(ID:=755).Index
Application.CommandBars("cell").Controls. _
Add Type:=msoControlButton, ID:=370, before:=Num
End Sub

Sub Delete_Paste_Special_Button()
On Error Resume Next
Application.CommandBars("cell").FindControl(ID:=370).Delete
On Error GoTo 0
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

From the msword forum I see a reference to
http://www.word.mvps.org/FAQs/Customization/AsgnCmdOrMacroToToolbar.htm,
which shows a way to add an item to the right click menu or what seems to be
more properly called shortcut menu.

I am trying to do the same thing in excel but in Tools->Customize->Toolbars
there is not such thing as "shortcut menu" used by the above referenced
technique, or at least I have not found it. However, from
Tools->Customize->Commands (then choose "Edit" on the left hand pane), I was
able to find the actual "paste values" menu item and have successfully
dragged & dropped it to my File->Edit menu.

I did see the post in this forum on "PAStE SPECIAL" [sic] by Dave Peterson
on 2007/02/24 on how to assign a macro to a keystroke shortcut. However
actually adding that option menu item to the right click menu is preferred
since it: a) is a personal preference, and b) much more importantly, avoids
the inability to undo issue noted by another user in the "PAStE SPECIAL"
thread.

So my two questions:

1) How do you add the "paste values" menu item onto the right click menu (or
the shortcut menu, although that term might not be used within the excel
context)?

2) How do you add the "paste values" menu item onto ALL THE right click
menus since it appears that the right click menu changes depending on context.

Thanks a lot.

 

[Ron de Bruin]

You are welcome Paul

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Ron, you've helped big time with this code - thanks.

Hi rockhammer

Try this

You can run the first macro one time to add it to the Cell menu for always

Sub Add_Paste_Special_Button()
' This will add the Paste Special Values button to the cell menu
' after the Paste option
Dim Num As Long
Num = Application.CommandBars("Cell"). _
FindControl(ID:=755).Index
Application.CommandBars("cell").Controls. _
Add Type:=msoControlButton, ID:=370, before:=Num
End Sub

Sub Delete_Paste_Special_Button()
On Error Resume Next
Application.CommandBars("cell").FindControl(ID:=370).Delete
On Error GoTo 0
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

From the msword forum I see a reference to
http://www.word.mvps.org/FAQs/Customization/AsgnCmdOrMacroToToolbar.htm,
which shows a way to add an item to the right click menu or what seems to be
more properly called shortcut menu.

I am trying to do the same thing in excel but in Tools->Customize->Toolbars
there is not such thing as "shortcut menu" used by the above referenced
technique, or at least I have not found it. However, from
Tools->Customize->Commands (then choose "Edit" on the left hand pane), I was
able to find the actual "paste values" menu item and have successfully
dragged & dropped it to my File->Edit menu.

I did see the post in this forum on "PAStE SPECIAL" [sic] by Dave Peterson
on 2007/02/24 on how to assign a macro to a keystroke shortcut. However
actually adding that option menu item to the right click menu is preferred
since it: a) is a personal preference, and b) much more importantly, avoids
the inability to undo issue noted by another user in the "PAStE SPECIAL"
thread.

So my two questions:

1) How do you add the "paste values" menu item onto the right click menu (or
the shortcut menu, although that term might not be used within the excel
context)?

2) How do you add the "paste values" menu item onto ALL THE right click
menus since it appears that the right click menu changes depending on context.

Thanks a lot.