Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Altoona, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Menu Items

    I would like to distribute a collection of macros to various users within the company I work for and have been intrigued by the several postings on creating Add-Ins. One issue though, Is that I normally create a custom menu group with several menu items each calling a different macro from the workbook. Is there a way to automate the creation and linking of these menu items? I'm thinking along the lines of the Workbook Open event triggering a serch for the menu items, and creating them if not found. I have tried recording the creation of the menu items and got very little. Help has not been too helpful as well. Any info is appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Menu Items

    You are exxetially correct. Unfortunately, my application that does something similar works from a Template rather than an Addin, and it modifies the standard Excel menues rather than creating custom menu bars, so I don't have any handy examples that I could post for you. In my case, workbooks created from the template use the Workbook Activate Event routine to save the users original configuration, and build my custom menues and tool bars. It also uses the Workbook Deactivate Event to restore the user's configuration. In your case, if you want the menus and/or tool bars available any time the addin is available, you would probably want to use the Workbook Open event in the addin. If you have problems writing that code, get back to us with more specifice questions, and I am sure someone around here will be able to help.
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Menu Items

    In my add-ins, I use code as below to add or delete a menu-item. In the case below, I added a new menuitem under the Tools menu (id:=30007). I use the id but you can use "Tools" if you are sure that your application will not be used by non-English users. Coomandbars has an index referring to the standard Excel menubar (= 1) or other menubars (e.g. the chart menubar is = 2). Then there are many different properties and methods for a commandbarbutton or commandbarpopup. If you go to the help of vba looking for these commandbar types, you will find some additional information. The books of John Walkenbach and John Green give very nice examples of creating menus and toolbars.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeleteMenuItem
    End Sub

    Private Sub Workbook_Open()
    AddMenuItem
    End Sub

    Sub DeleteMenuItem()
    On Error Resume Next
    Application.CommandBars(1).FindControl(Id:=30007). Controls("&MyMacro").Delete
    End Sub

    Sub AddMenuItem()
    Dim ToolsMenu As CommandBarPopup
    Dim NewMenuItem As CommandBarButton
    Call DeleteMenuItem
    Set ToolsMenu = Application.CommandBars(1).FindControl(Id:=30007)
    If ToolsMenu Is Nothing Then
    MsgBox "Cannot add menu item"
    Exit Sub
    Else
    Set NewMenuItem = ToolsMenu.Controls.Add _
    (Type:=msoControlButton)
    With NewMenuItem
    .Caption = "&MyMacro"
    .FaceId = 348
    .OnAction = "LaunchMyMacro" 'this is the name of the macro subroutine
    .BeginGroup = True
    End With
    End If
    End Sub


    Hope this helps.

Posting Permissions

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