Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: code to add item to menu bar

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm trying to create an add-in and add it as an item on the menu bar. I copied the code from an Excel book. I was doing an F8 to check each line of code and when I get to the line:
    Application.CommandBars(1).Controls("&Accounting Dept").Controls.Add
    I get a message "Object doesn't support this property or method". I copied it exactly except for changing the names. I'm trying to understand the logic but still new at this.

    Const MenuItemName = "Income Accruals"
    Const MenuItemMacro = "GetForm"

    Private Sub Workbook_Open()
    ' Create a new menu item
    Set NewMenu = Application.CommandBars(1).Controls.Add
    ' Add a caption
    NewMenu.Caption = "&Accounting Dept"
    ' Create a new menu item
    Set NewItem = Application.CommandBars(1).Controls("&Accounting Dept").Controls.Add
    ' Specify the Caption and OnAction properties
    NewItem.Caption = MenuItemName
    NewItem.OnAction = MenuItemMacro
    ' Add separator bar before the menu item
    NewItem.BeginGroup = True
    End Sub
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Delete existing item before close
    Application.CommandBars(1).Controls("&Accounting Dept").Controls(MenuItemName).Delete
    End Sub

    I appreciate any help.

    Thanks.

    Ricardo

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have a look at the link below, the 'Menumakr' download should help you out: -

    http://j-walk.com/ss/excel/tips/tip53.htm

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mudface,

    I'm starting out very simply with a new item on the menu bar with one submenu (in Menumakr, Level 1 & 2, no 3). Can I just modify the first two rows on the MenuSheet and delete the rest? I want to make sure if its that simple.

    Thanks.

    Ricardo

    [ This Message was edited by: Ricardo2 on 2002-04-15 11:53 ]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following should do what you want: -

    Private Sub Workbook_Open()

    Call CreateMenu

    End Sub

    ' Put the following in a standard module

    Option Explicit
    Const MenuItemName = "Income Accruals"
    Const MenuItemMacro = "GetForm"

    Public Sub CreateMenu()

    Dim MenuObject As CommandBarPopup
    Dim MenuItem As CommandBarButton

    On Error Resume Next
    Application.CommandBars(1).Controls("&Accounting Dept").Delete
    On Error GoTo 0

    Set MenuObject = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, _
    Temporary:=True)
    MenuObject.Caption = "&Accounting Dept"

    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
    With MenuItem
    .Caption = MenuItemName
    .OnAction = MenuItemMacro
    .BeginGroup = True
    End With

    End Sub


    HTH .

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mudface,

    I modified the MenuSheet using only the two rows I needed. It works!

    Menumakr rocks!

    Thanks for the help!

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The MenuMaker worked great. I have a follow up question however. Is there any way I can deactivate one of the menu choices based on what sheet the user is on? If I could do this, that would be awesome. Any suggestions?
    Thanks in advance,
    Patrick

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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