Re: add "paste values" to right click menu

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"rockhammer" <rockhammer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:89EF43F4-942E-4752-85C2-DAF1F8BF0D11@xxxxxxxxxxxxxxxx
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 spread*** I develop without having to repeat the code in each spread***. Is there a way to do that?

Thanks a lot.



"Ron de Bruin" wrote:

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


"rockhammer" <rockhammer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:BB1360E6-E1C8-41D9-9CA9-40FEEB955B64@xxxxxxxxxxxxxxxx
> 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.
> > >

.

Quantcast