Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Trying to Add New Menu Item to Menu Bar

  1. #1
    Board Regular
    Join Date
    May 2018
    Location
    South Texas (On the Beach)
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Trying to Add New Menu Item to Menu Bar

    Below is the code I currently have. I don't get any errors but nothing happens (that I can see anyway)

    Pretty sure the code is running because I was getting an error in beforeclose until I add the On Error Resume Next

    Running Excel 2016

    Thanks in advance
    Code:
    Private Sub Workbook_Open()
    Dim mymenubar As CommandBar
    Dim newmenu As CommandBarPopup
    Dim ctrl1, ctrl2 As CommandBarButton
    On Error Resume Next
       
    Set mymenubar = Application.CommandBars("Worksheet menu Bar")
    Set newmenu = mymenubar.Controls.Add(Type:=msoControlPopup, Temporary:=True, before:=2)
    newmenu.Caption = "SetBilder"
    Set ctrl1 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
        With ctrl1
            .Caption = "Print All"
            .Style = msoButtonCaption
            .OnAction = "'" & ThisWorkbook.Name & "'!printall"
        End With
            
    Set ctrl2 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
        With ctrl2
            .Caption = "Print No Cost"
            .Style = msoButtonCaption
            .OnAction = "'" & ThisWorkbook.Name & "'!printnocost"
        End With
    End Sub
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim CmdBar As CommandBar
    Dim CmdBarMenu As CommandBarControl
    Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
    Set CmdBarMenu = CmdBar.Controls("SetBilder")
    CmdBarMenu.Delete
    End Sub
    Last edited by RoryA; Jun 13th, 2018 at 09:14 AM.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,229
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Trying to Add New Menu Item to Menu Bar

    If you open your workbook and make sure that macros are enabled, it should add a new tab called "Add-Ins", where you should see your new menu. If not, try removing "On Error Resume Next" from the workbook open event handler, save the workbook, close, and re-open it. Do you now get error? if so, which one? And, on which line?

  3. #3
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    1,936
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to Add New Menu Item to Menu Bar

    .
    This version of your macro runs without error here :

    Code:
    Option Explicit
    
    
    Private Sub Workbook_Open()
    Dim mymenubar As CommandBar
    Dim newmenu As CommandBarPopup
    Dim ctrl1, ctrl2 As CommandBarButton
    On Error Resume Next
       
    Set mymenubar = Application.CommandBars("Worksheet menu Bar")
    Set newmenu = mymenubar.Controls.Add(Type:=msoControlPopup, Temporary:=True, before:=2)
    newmenu.Caption = "SetBuilder"
    Set ctrl1 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
        With ctrl1
            .Caption = "Print All"
            .Style = msoButtonCaption
            .OnAction = "'" & ThisWorkbook.Name & "'!printall"
        End With
            
    Set ctrl2 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
        With ctrl2
            .Caption = "Print No Cost"
            .Style = msoButtonCaption
            .OnAction = "'" & ThisWorkbook.Name & "'!printnocost"
        End With
    End Sub
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim CmdBar As CommandBar
    Dim CmdBarMenu As CommandBarControl
    Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
    Set CmdBarMenu = CmdBar.Controls("SetBuilder")
    CmdBarMenu.Delete
    End Sub

  4. #4
    Board Regular
    Join Date
    May 2018
    Location
    South Texas (On the Beach)
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to Add New Menu Item to Menu Bar

    Without the On Error Resume I get the error in the Workbook Before Close on CmdBarMenu.Delete command so I know it is running. If I uses the addins menu to enable the Menu will it not make the menu active for all Excel Workbooks? - I need it to be open only for this one

  5. #5
    Board Regular
    Join Date
    May 2018
    Location
    South Texas (On the Beach)
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to Add New Menu Item to Menu Bar

    This ran in your Excel without doing anything except copy and Paste?????

  6. #6
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    1,936
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to Add New Menu Item to Menu Bar

    .
    [COLOR=#3 33333]This ran in your Excel without doing anything except copy and Paste?????[/COLOR]
    No ... it ran after I removed the "On Error Resume Next", then corrected one or two small corrections. I don't recall what they were now. Guess I could go back and re-run your original
    macro again to see.

    However, as Domenic indicated, make certain you have MACROS ENABLED in the EXCEL SETTINGS.

    You should be able to simply copy the code I posted in #3 and make it run.

  7. #7
    Board Regular
    Join Date
    May 2018
    Location
    South Texas (On the Beach)
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to Add New Menu Item to Menu Bar

    hmmm...The Macros themselves are running as I can see them when I click "Macros" in Developer Tab and they do function if I select and run. The Part that is not happening is the Menu entitled "SetBilder" does not appear in the Tabs (Menus)

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,703
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Trying to Add New Menu Item to Menu Bar

    Your code works fine for me, as posted. It adds the menu item to the AddIns tab, as Domenic mentioned. There is no other option for that using VBA.

  9. #9
    Board Regular
    Join Date
    May 2018
    Location
    South Texas (On the Beach)
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to Add New Menu Item to Menu Bar

    Yes I have the "Addins" Tab Checked - Still no Menu in Menu Bar?? I'm struggling here as to why this doesnt work??






  10. #10
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,703
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Trying to Add New Menu Item to Menu Bar

    If you can see the Addins tab, what is on it?

Some videos you may like

User Tag List

Tags for this Thread

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
  •