+ Reply to Thread
Results 1 to 5 of 5

Customize the right-click menu

  1. #1
    quartz
    Guest

    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. #2
    Doug Glancy
    Guest

    Re: 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, 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" <quartz@discussions.microsoft.com> wrote in message
    news:D7BE674D-FDF6-42B9-8A17-8CAACB488BF8@microsoft.com...
    > 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. #3
    Gary Brown
    Guest

    RE: 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, 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.


  4. #4
    quartz
    Guest

    RE: 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, 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.


  5. #5
    Gary Brown
    Guest

    RE: 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, 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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1