+ Reply to Thread
Results 1 to 5 of 5

Creating two menus

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    11-16-2004
    Location
    Devon UK
    MS-Off Ver
    2010
    Posts
    357

    Creating two menus

    I have a workbook which uses the code below to create a new menu upon opening. I have now used this same code in a second workbook to create a different menu. both work OK on their own However if by chance both workbooks are opened at the same time only one menu is shown.
    Is there a way I can change one of these to create a second menu if the first one is already there?

    Option Explicit
    Sub CreateMenu()
    
    Dim cbMenu As CommandBarControl, cbSubMenu As CommandBarControl
        RemoveMenu ' delete the menu if it already exists
        ' create a new menu on an existing commandbar
          
        
        Set cbMenu = Application.CommandBars(1).Controls.Add(msoControlPopup, , , , True)
        With cbMenu
            .Caption = "&Menu title"
            .Tag = "MyTag"
            .BeginGroup = False
        End With
        If cbMenu Is Nothing Then Exit Sub ' didn't find the menu...
        
        
         Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
        With cbSubMenu
            .Caption = "&First sub menu"
            .Tag = "SubMenu1"
            .BeginGroup = True
        End With
    
       '  add menuitem to submenu
        With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
            .Caption = "&First action"
            .OnAction = "macro1"
            .Style = msoButtonIconAndCaption
            .FaceId = 71
            .State = msoButtonDown ' or msoButtonUp
        End With
    
        ' add menuitem to submenu
        With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
            .Caption = "&Second action"
            .OnAction = "macro2"
            .Style = msoButtonIconAndCaption
            .FaceId = 72
           .Enabled = True '
        End With
        
           Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
        With cbSubMenu
            .Caption = "&second submenu"
            .Tag = "Macro3"
            .BeginGroup = True
        End With
        
          'add a submenu
         With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
       'With cbSubMenu
            .Caption = "&etc"
            .Tag = "SubMenu1"
            .OnAction = "etc"
            .BeginGroup = True
        End With
    
        ' add menuitem to submenu
        With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
            .Caption = "&etc"
            .OnAction = "etc"
            .Style = msoButtonIconAndCaption
            .FaceId = 71
            .State = msoButtonUp '
        End With
        
         ' add menuitem to submenu
        With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
            .Caption = "&etc"
            .OnAction = "etc"
            .Style = msoButtonIconAndCaption
            .FaceId = 71
            .State = msoButtonUp '
        End With
          ' add menuitem to submenu
        With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
            .Caption = "&etc"
            .OnAction = "etc"
            .Style = msoButtonIconAndCaption
            .FaceId = 71
            .State = msoButtonUp '
        End With
        
          Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
        With cbSubMenu
            .Caption = "&etc"
            .Tag = "SubMenu1"
            .BeginGroup = True
        End With
        
            ' add menuitem to submenu
        With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
            .Caption = "&etc"
            .OnAction = "etc"
            .Style = msoButtonIconAndCaption
            .FaceId = 71
            .State = msoButtonUp '
        End With
        
            ' add menuitem to submenu
        With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
            .Caption = "&etc"
            .OnAction = "etc"
            .Style = msoButtonIconAndCaption
            .FaceId = 71
            .State = msoButtonUp '
        End With
    
        ' add menuitem to submenu
        'With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
           ' .Caption = "&etc"
            '.OnAction = "etc"
            '.Style = msoButtonIconAndCaption
            '.FaceId = 72
            '.Enabled = True '
       ' End With
           ' add menuitem to submenu
       
    End Sub
    Sub RemoveMenu()
    
        DeleteCustomCommandBarControl "MyTag" ' deletes the new menu
    End Sub
    Private Sub DeleteCustomCommandBarControl(CustomControlTag As String)
    ' deletes ALL occurences of commandbar controls with a tag = CustomControlTag
        On Error Resume Next
        Do
            Application.CommandBars.FindControl(, , CustomControlTag, False).Delete
        Loop Until _
            Application.CommandBars.FindControl(, , CustomControlTag, False) Is Nothing
        On Error GoTo 0
    End Sub
    Last edited by tryer; 10-12-2009 at 10:16 AM.

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Creating two menus

    what about since the menus have the same name add
    Private Sub Workbook_Deactivate()
    Call CreateMenu
    End Sub
    Private Sub Workbook_Activate()
    Call RemoveMenu
    End Sub
    to each workbook
    then the subs will only work for the open work book
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Valued Forum Contributor
    Join Date
    11-16-2004
    Location
    Devon UK
    MS-Off Ver
    2010
    Posts
    357

    Re: Creating two menus

    Thanks for the reply Pike. The thing is I need to be able to have both menu's available if both workbooks are open. However your comments have made me think about names, and I realise that the createmenu sub was named the same in both workbooks so I have cheanged one of them but unfotunately it hasn't cured the problem. When I open the second workbook the first menu is overwritten.

    Any further thoughts?

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,447

    Re: Creating two menus

    You need to give the menus a different Tag value.

    This part removes a menu with the tag "MyTag" in your case they both have the same tag.

    Sub RemoveMenu()
    
        DeleteCustomCommandBarControl "MyTag" ' deletes the new menu
    End Sub
    Don't forget to also change the tag when creating the menu.
    Cheers
    Andy
    www.andypope.info

  5. #5
    Valued Forum Contributor
    Join Date
    11-16-2004
    Location
    Devon UK
    MS-Off Ver
    2010
    Posts
    357

    Re: Creating two menus

    Brilliant !!

    Thank you Andy that solved it

+ 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