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.

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, before:=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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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, Before:=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.

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, Before:=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.

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, Before:=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.