Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
add "paste values" to right click menu
From the msword forum I see a reference to
http://www.word.mvps.org/FAQs/Custom...oToToolbar.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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
add "paste values" to right click menu
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, befo=Num End Sub Sub Delete_Paste_Special_Button() On Error Resume Next Application.CommandBars("cell").FindControl(ID:=37 0).Delete On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "rockhammer" wrote in message ... From the msword forum I see a reference to http://www.word.mvps.org/FAQs/Custom...oToToolbar.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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
add "paste values" to right click menu
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. "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, befo=Num End Sub Sub Delete_Paste_Special_Button() On Error Resume Next Application.CommandBars("cell").FindControl(ID:=37 0).Delete On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "rockhammer" wrote in message ... From the msword forum I see a reference to http://www.word.mvps.org/FAQs/Custom...oToToolbar.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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
add "paste values" to right click menu
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, befo=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" wrote in message ... 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. "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, befo=Num End Sub Sub Delete_Paste_Special_Button() On Error Resume Next Application.CommandBars("cell").FindControl(ID:=37 0).Delete On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "rockhammer" wrote in message ... From the msword forum I see a reference to http://www.word.mvps.org/FAQs/Custom...oToToolbar.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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
add "paste values" to right click menu
Thanks for the amazingly fast responses! Your help is much appreicated.
"Ron de Bruin" wrote: 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, befo=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" wrote in message ... 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. "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, befo=Num End Sub Sub Delete_Paste_Special_Button() On Error Resume Next Application.CommandBars("cell").FindControl(ID:=37 0).Delete On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "rockhammer" wrote in message ... From the msword forum I see a reference to http://www.word.mvps.org/FAQs/Custom...oToToolbar.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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
add "paste values" to right click menu
Ron, you've helped big time with this code - thanks.
"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, befo=Num End Sub Sub Delete_Paste_Special_Button() On Error Resume Next Application.CommandBars("cell").FindControl(ID:=37 0).Delete On Error GoTo 0 End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "rockhammer" wrote in message ... From the msword forum I see a reference to http://www.word.mvps.org/FAQs/Custom...oToToolbar.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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I have "Shift-Click" or "Ctrl-Click" Code on Form List? | Excel Programming | |||
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F | Excel Discussion (Misc queries) | |||
It's not CommandBars("Cell"), right-click menu! | Excel Programming | |||
What does the "Paste Special Dropdown" popup menu do? | Excel Programming | |||
disable right mouse click on "Worksheet Menu Bar" | Excel Programming |