Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Customize the right-click menu
I am using Office 2003 on Windows XP.
I just want to add a single custom control as the first choice on Excel's built-in right-click shortcut spreadsheet menu ("Cell"). I want all the functions to work as normal, but I only want it to work in the current file. Since changing the menu affects all files, I think the menu needs to be rebuilt with my custom control (a button) at the top. Can anyone offer any suggestions on how to do this? Please post example code. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Customize the right-click menu
quartz,
Here' how I do it: In the ThisWorkBook module add these three procedures: Private Sub Workbook_Open() Call AddShortcutMenuItems 'install custom menu items End Sub Private Sub Workbook_Activate() Call AddShortcutMenuItems 'install custom menu items End Sub Private Sub Workbook_Deactivate() Call DeleteShortcutMenuItems ' when closing or moving to other workbook, if close cancelled then this doesn't happen End Sub Now in a regular module, add these two procedures. I'd call this module something like MenuMacros: Sub AddShortcutMenuItems() Dim new_menu_item As CommandBarButton Dim i As Integer Call DeleteShortcutMenuItems 'in case the items already there 'Add items to the Cell shortcut menu With Application.CommandBars("Cell") Set new_menu_item = .Controls.Add(Type:=msoControlButton, befo=1) With new_menu_item .Caption = "&Test Button" .OnAction = "test_button_macro" .Style = msoButtonIconAndCaption .FaceId = 8 End With End With End Sub Sub DeleteShortcutMenuItems() On Error Resume Next 'in case the item isn't there With Application.CommandBars("Cell") .Controls("&Test Button").Delete End With End Sub Now add the button click procedure - I'd do it in a 3rd module but it could be in the menu module too: Sub test_button_macro() msgbox "testing" End Sub hth, Doug "quartz" wrote in message ... I am using Office 2003 on Windows XP. I just want to add a single custom control as the first choice on Excel's built-in right-click shortcut spreadsheet menu ("Cell"). I want all the functions to work as normal, but I only want it to work in the current file. Since changing the menu affects all files, I think the menu needs to be rebuilt with my custom control (a button) at the top. Can anyone offer any suggestions on how to do this? Please post example code. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Customize the right-click menu
In the 'ThisWorkbook' module of your file put...
'/==================================================/ Private Sub Workbook_Activate() 'create menu item on at top of Cell menu ' [Right click on a cell] Dim cbcMenuItem As CommandBarControl On Error Resume Next Set cbcMenuItem = _ Application.CommandBars("CELL").Controls. _ Add(Type:=1, Befo=1, Temporary:=True) 'Type 1 = msoControlButton With cbcMenuItem .Caption = "&My_Menu_Item" .OnAction = ThisWorkbook.Name & "!My_Macro" End With Set cbcMenuItem = Nothing End Sub '/==================================================/ Private Sub Workbook_Deactivate() 'delete menu item on Cell menu Dim strCaption As String On Error Resume Next strCaption = "&My_Menu_Item" Application.CommandBars("CELL"). _ Controls(strCaption).Delete End Sub '/==================================================/ In a regular module, put your macro Example: '/==================================================/ Public Sub My_Macro() MsgBox "Hello" End Sub '/==================================================/ - - - - - Items that you can change. Caption of "&My_Menu_Item" can be changed to anything you want OnAction points to the macro in the regular module. You can change 'My_Macro' to something else HTH, Gary Brown "quartz" wrote: I am using Office 2003 on Windows XP. I just want to add a single custom control as the first choice on Excel's built-in right-click shortcut spreadsheet menu ("Cell"). I want all the functions to work as normal, but I only want it to work in the current file. Since changing the menu affects all files, I think the menu needs to be rebuilt with my custom control (a button) at the top. Can anyone offer any suggestions on how to do this? Please post example code. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Customize the right-click menu
Thanks Gary! It works well. I took the liberty of just using the "Reset"
command when the workbook is deactivated rather than deleting the control (the effect is the same). Thanks much. "Gary Brown" wrote: In the 'ThisWorkbook' module of your file put... '/==================================================/ Private Sub Workbook_Activate() 'create menu item on at top of Cell menu ' [Right click on a cell] Dim cbcMenuItem As CommandBarControl On Error Resume Next Set cbcMenuItem = _ Application.CommandBars("CELL").Controls. _ Add(Type:=1, Befo=1, Temporary:=True) 'Type 1 = msoControlButton With cbcMenuItem .Caption = "&My_Menu_Item" .OnAction = ThisWorkbook.Name & "!My_Macro" End With Set cbcMenuItem = Nothing End Sub '/==================================================/ Private Sub Workbook_Deactivate() 'delete menu item on Cell menu Dim strCaption As String On Error Resume Next strCaption = "&My_Menu_Item" Application.CommandBars("CELL"). _ Controls(strCaption).Delete End Sub '/==================================================/ In a regular module, put your macro Example: '/==================================================/ Public Sub My_Macro() MsgBox "Hello" End Sub '/==================================================/ - - - - - Items that you can change. Caption of "&My_Menu_Item" can be changed to anything you want OnAction points to the macro in the regular module. You can change 'My_Macro' to something else HTH, Gary Brown "quartz" wrote: I am using Office 2003 on Windows XP. I just want to add a single custom control as the first choice on Excel's built-in right-click shortcut spreadsheet menu ("Cell"). I want all the functions to work as normal, but I only want it to work in the current file. Since changing the menu affects all files, I think the menu needs to be rebuilt with my custom control (a button) at the top. Can anyone offer any suggestions on how to do this? Please post example code. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Customize the right-click menu
The only issue with 'Reset' is when you have other customized button(s) on
your menu. Resetting wipes out the other button(s) (of yours or from another add-in). It's kind of a shotgun approach. Deleting the specific button is the handgun approach. Either method seems fine for what you want to do. Good Luck. Sincerely, Gary Brown "quartz" wrote: Thanks Gary! It works well. I took the liberty of just using the "Reset" command when the workbook is deactivated rather than deleting the control (the effect is the same). Thanks much. "Gary Brown" wrote: In the 'ThisWorkbook' module of your file put... '/==================================================/ Private Sub Workbook_Activate() 'create menu item on at top of Cell menu ' [Right click on a cell] Dim cbcMenuItem As CommandBarControl On Error Resume Next Set cbcMenuItem = _ Application.CommandBars("CELL").Controls. _ Add(Type:=1, Befo=1, Temporary:=True) 'Type 1 = msoControlButton With cbcMenuItem .Caption = "&My_Menu_Item" .OnAction = ThisWorkbook.Name & "!My_Macro" End With Set cbcMenuItem = Nothing End Sub '/==================================================/ Private Sub Workbook_Deactivate() 'delete menu item on Cell menu Dim strCaption As String On Error Resume Next strCaption = "&My_Menu_Item" Application.CommandBars("CELL"). _ Controls(strCaption).Delete End Sub '/==================================================/ In a regular module, put your macro Example: '/==================================================/ Public Sub My_Macro() MsgBox "Hello" End Sub '/==================================================/ - - - - - Items that you can change. Caption of "&My_Menu_Item" can be changed to anything you want OnAction points to the macro in the regular module. You can change 'My_Macro' to something else HTH, Gary Brown "quartz" wrote: I am using Office 2003 on Windows XP. I just want to add a single custom control as the first choice on Excel's built-in right-click shortcut spreadsheet menu ("Cell"). I want all the functions to work as normal, but I only want it to work in the current file. Since changing the menu affects all files, I think the menu needs to be rebuilt with my custom control (a button) at the top. Can anyone offer any suggestions on how to do this? Please post example code. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Customize the right-click menu
Gary,
I saw this posting and it does exactly what I need with one exception. How do I sort the list or place my special menu item in a certain position (i.e. 2nd from top) Thanks "Gary Brown" wrote: In the 'ThisWorkbook' module of your file put... '/==================================================/ Private Sub Workbook_Activate() 'create menu item on at top of Cell menu ' [Right click on a cell] Dim cbcMenuItem As CommandBarControl On Error Resume Next Set cbcMenuItem = _ Application.CommandBars("CELL").Controls. _ Add(Type:=1, Befo=1, Temporary:=True) 'Type 1 = msoControlButton With cbcMenuItem .Caption = "&My_Menu_Item" .OnAction = ThisWorkbook.Name & "!My_Macro" End With Set cbcMenuItem = Nothing End Sub '/==================================================/ Private Sub Workbook_Deactivate() 'delete menu item on Cell menu Dim strCaption As String On Error Resume Next strCaption = "&My_Menu_Item" Application.CommandBars("CELL"). _ Controls(strCaption).Delete End Sub '/==================================================/ In a regular module, put your macro Example: '/==================================================/ Public Sub My_Macro() MsgBox "Hello" End Sub '/==================================================/ - - - - - Items that you can change. Caption of "&My_Menu_Item" can be changed to anything you want OnAction points to the macro in the regular module. You can change 'My_Macro' to something else HTH, Gary Brown "quartz" wrote: I am using Office 2003 on Windows XP. I just want to add a single custom control as the first choice on Excel's built-in right-click shortcut spreadsheet menu ("Cell"). I want all the functions to work as normal, but I only want it to work in the current file. Since changing the menu affects all files, I think the menu needs to be rebuilt with my custom control (a button) at the top. Can anyone offer any suggestions on how to do this? Please post example code. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Customize the right-click menu
There should be two Before Arguments?
-- Regards, Tom Ogilvy "Gary Brown" wrote in message ... Hi Rafi, Change... Set cbcMenuItem = _ Application.CommandBars("CELL").Controls. _ Add(Type:=1, Befo=1, Temporary:=True) To... Set cbcMenuItem = _ Application.CommandBars("CELL").Controls. _ Add(Type:=1, Befo=1, Temporary:=True, Befo=2) -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. "Rafi" wrote: Gary, I saw this posting and it does exactly what I need with one exception. How do I sort the list or place my special menu item in a certain position (i.e. 2nd from top) Thanks "Gary Brown" wrote: In the 'ThisWorkbook' module of your file put... '/==================================================/ Private Sub Workbook_Activate() 'create menu item on at top of Cell menu ' [Right click on a cell] Dim cbcMenuItem As CommandBarControl On Error Resume Next Set cbcMenuItem = _ Application.CommandBars("CELL").Controls. _ Add(Type:=1, Befo=1, Temporary:=True) 'Type 1 = msoControlButton With cbcMenuItem .Caption = "&My_Menu_Item" .OnAction = ThisWorkbook.Name & "!My_Macro" End With Set cbcMenuItem = Nothing End Sub '/==================================================/ Private Sub Workbook_Deactivate() 'delete menu item on Cell menu Dim strCaption As String On Error Resume Next strCaption = "&My_Menu_Item" Application.CommandBars("CELL"). _ Controls(strCaption).Delete End Sub '/==================================================/ In a regular module, put your macro Example: '/==================================================/ Public Sub My_Macro() MsgBox "Hello" End Sub '/==================================================/ - - - - - Items that you can change. Caption of "&My_Menu_Item" can be changed to anything you want OnAction points to the macro in the regular module. You can change 'My_Macro' to something else HTH, Gary Brown "quartz" wrote: I am using Office 2003 on Windows XP. I just want to add a single custom control as the first choice on Excel's built-in right-click shortcut spreadsheet menu ("Cell"). I want all the functions to work as normal, but I only want it to work in the current file. Since changing the menu affects all files, I think the menu needs to be rebuilt with my custom control (a button) at the top. Can anyone offer any suggestions on how to do this? Please post example code. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Customize the right-click menu
Good catch Tom :O
To... Set cbcMenuItem = _ Application.CommandBars("CELL").Controls. _ Add(Type:=1, Temporary:=True, Befo=2) -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. "Tom Ogilvy" wrote: There should be two Before Arguments? -- Regards, Tom Ogilvy "Gary Brown" wrote in message ... Hi Rafi, Change... Set cbcMenuItem = _ Application.CommandBars("CELL").Controls. _ Add(Type:=1, Befo=1, Temporary:=True) To... Set cbcMenuItem = _ Application.CommandBars("CELL").Controls. _ Add(Type:=1, Befo=1, Temporary:=True, Befo=2) -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. "Rafi" wrote: Gary, I saw this posting and it does exactly what I need with one exception. How do I sort the list or place my special menu item in a certain position (i.e. 2nd from top) Thanks "Gary Brown" wrote: In the 'ThisWorkbook' module of your file put... '/==================================================/ Private Sub Workbook_Activate() 'create menu item on at top of Cell menu ' [Right click on a cell] Dim cbcMenuItem As CommandBarControl On Error Resume Next Set cbcMenuItem = _ Application.CommandBars("CELL").Controls. _ Add(Type:=1, Befo=1, Temporary:=True) 'Type 1 = msoControlButton With cbcMenuItem .Caption = "&My_Menu_Item" .OnAction = ThisWorkbook.Name & "!My_Macro" End With Set cbcMenuItem = Nothing End Sub '/==================================================/ Private Sub Workbook_Deactivate() 'delete menu item on Cell menu Dim strCaption As String On Error Resume Next strCaption = "&My_Menu_Item" Application.CommandBars("CELL"). _ Controls(strCaption).Delete End Sub '/==================================================/ In a regular module, put your macro Example: '/==================================================/ Public Sub My_Macro() MsgBox "Hello" End Sub '/==================================================/ - - - - - Items that you can change. Caption of "&My_Menu_Item" can be changed to anything you want OnAction points to the macro in the regular module. You can change 'My_Macro' to something else HTH, Gary Brown "quartz" wrote: I am using Office 2003 on Windows XP. I just want to add a single custom control as the first choice on Excel's built-in right-click shortcut spreadsheet menu ("Cell"). I want all the functions to work as normal, but I only want it to work in the current file. Since changing the menu affects all files, I think the menu needs to be rebuilt with my custom control (a button) at the top. Can anyone offer any suggestions on how to do this? Please post example code. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I customize a right click to find data? | Excel Worksheet Functions | |||
disable customize option when right click on menu bar | Excel Discussion (Misc queries) | |||
how can you customize a shortcut menu in excel? | Excel Discussion (Misc queries) | |||
Adding a menu item right click menu when clicking on a single. | Excel Programming | |||
customize right click | Excel Programming |