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.
|