Modifying the Menu Bar

[Don Wells]

    I am thinking of a new Add-In that will require a pop-up with subordinate pop-ups and command buttons to be placed in one of the existing menu bar menus at the second or third subordinate level.
    When the add-in is opened I expect to modify the existing Menu item (which may or may not have been modified by other add-ins).
    When the add-in is closed I expect to remove all of its menu items from the menu bar, recognizing that other changes may have been made to that branch of the menu bar during the time my add-in was open. There may even have been pop-ups and command buttons added to the branch which my add-in created.
    I know how to create a command bar complete with command buttons and pop-ups etc. but have no idea how to integrate this structure into an existing menu bar and later remove it in a sanitary fashion. Any guidance will be greatly appreciated. Additionally I would appreciate any insight into any existing protocol with respect to handling buttons which other programs have loaded onto a branch which you created and are about to revise or delete.

[HansV]

It's OK to add a new menu to the menu bar, with as many items and submenus as you like. Since it's your menu (you should give it a distinctive name), there is no reason to assume that other add-ins will have modified it while your add-in is active, so you can delete the menu when the add-in is closed.

The same goes for a custom toolbar. Since it's yours, you can delete it without problem.

Users with Excel 2007 or later will see the new menu or toolbar in the Add-Ins tab of the ribbon.

I wouldn't recommend adding new items to existing menus/toolbars or modifying existing menus/toolbars - as far as I know they wouldn't be available to users of Excel 2007 and later.

[Don Wells]

Thanks Hans

Points taken. Can you dvise on how to add a new menu to the menu bar and how to control its position?

[HansV]

Which application is it for? Excel? If so, you can use code like this:

Code: Select all

Sub MakeMenu()
  On Error Resume Next
  CommandBars("Worksheet Menu Bar").Controls("My &Menu").Delete
  On Error GoTo 0
  Dim cbp As CommandBarPopup
  Dim cbb As CommandBarButton
  Set cbp = CommandBars("Worksheet Menu Bar").Controls.Add( _
    Type:=msoControlPopup, Before:=5)
  With cbp
    .Visible = True
    .Caption = "My &Menu"
  End With
  Set cbb = cbp.Controls.Add(Type:=msoControlButton)
  With cbb
    .Caption = "Item &One"
    .Style = msoButtonCaption
    .OnAction = "MacroOne"
  End With
  Set cbb = cbp.Controls.Add(Type:=msoControlButton)
  With cbb
    .Caption = "Item &Two"
    .Style = msoButtonCaption
    .BeginGroup = True
    .OnAction = "MacroTwo"
  End With
End Sub

[Don Wells]

Which application is it for? Excel? If so, you can use code like this:

Yes Hans it is Excel in this instance. I happen to be using 2003, Your example says it all; I follow the logic.

[HansV]

Excel is unusual in that it has two main menu bars: "Worksheet Menu Bar" and "Chart Menu Bar". The chart menu bar automatically becomes active when a user selects a chart embedded in a worksheet or activates a chart sheet.

Depending on your purpose you may have to create your menu in one of these menu bars or in both.

[Don Wells]

[quote="HansV"]
    Is it possible using VBA, to copy a pop-up and all of its subordinate objects into a new menu item?

[HansV]

Yes, like this:

Dim cbp1 As CommandBarPopup
Dim cbp2 As CommandBarPopup
Set cbp1 = CommandBars("Worksheet Menu Bar").Controls("My &Menu")
Set cbp2 = cbp1.Copy

You can then modify cbp2.

[Don Wells]

Yes, like this:.

    Thank you Hans. I will probably be back for further demystifying.