modify the menu bar

**maacmaac** · 03-19-2009, 04:02 PM

I have some code that loads and unloads a command under the "DATA" menu bar.

Is it possible to add an item to the menu bar and put the commands under the new menu item?

For example:

Add "XYZ Consulting" as a menu bar item so it would like as follows:

File, Edit, Insert, Format, Tools, Data, Window, XYZ Consulting, Help.

Sub CreateMenu()

Dim Btn             As CommandBarButton

    Call DeleteMenu
    
    Set Btn = Application.CommandBars("Worksheet Menu Bar").Controls("Data").Controls.Add
    With Btn
        .Move Before:=1
        .Caption = "Trade &Analysis"
        .OnAction = "TradeAnalysis"
    End With

End Sub

Sub DeleteMenu()

    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Trade Analysis").Delete
    On Error GoTo 0

End Sub

**dominicb** · 03-19-2009, 05:02 PM

Good evening maacmaac

this should get you started, creating your own menu item and two sub items below it :

Sub CreateMenu()
Dim HelpMenu As CommandBarControl, NewMenu As CommandBarPopup
Dim MenuItem As CommandBarControl, Submenuitem As CommandBarButton
Set HelpMenu = CommandBars(1).FindControl(Id:=30010)
Set NewMenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, Before:=HelpMenu.Index, temporary:=True)
NewMenu.Caption = "Trade &Analysis"
Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
With MenuItem
.Caption = "Item 1"
'.FaceId = 123
.OnAction = "Macro1"
End With
Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
With MenuItem
.Caption = "Item 2"
'.FaceId = 123
.OnAction = "Macro2"
End With
End Sub

Sub DeleteMenu()
On Error Resume Next
CommandBars(1).Controls("Trade Analysis").Delete
End Sub

HTH

DominicB

**maacmaac** · 03-20-2009, 10:48 PM

That works great. It so much cleaner doing it this was as before I was putting button bars on other sheets and assigning macros to those. Just a couple of questions (1) what is the find control ID 30010? Where do I find a list of numbers if such a list exists? (2) you have .FaceId = 123 as comment. Why is this a comment and if it was not a comment, what would this part of the code do? Thanks

Sub CreateMenu()

Dim HelpMenu        As CommandBarControl
Dim NewMenu         As CommandBarPopup
Dim MenuItem        As CommandBarControl
Dim SubMenuItem     As CommandBarButton

Call DeleteMenu

Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
Set NewMenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, _
    Before:=HelpMenu.Index, temporary:=True)

NewMenu.Caption = "ABC &Reporting Group"

    Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
    With MenuItem
        .Caption = "Trade Analysis"
        '.FaceId = 123
        .OnAction = "TradeAnalysis"
    End With

    Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
    With MenuItem
        .Caption = "Item 2"
        '.FaceId = 123
        .OnAction = "Macro2"
    End With
    
End Sub

**maacmaac** · 03-20-2009, 11:02 PM

I just found the code to identify the face ID codes. Found the code from "The Spreadsheet Page". The link is http://spreadsheetpage.com/index.php...andbar_images/

I would still like more information on FindControl ID if anyone has it. Thanks.

**dominicb** · 03-21-2009, 04:24 AM

Hi maacmaac

Originally Posted by maacmaac

I would still like more information on FindControl ID if anyone has it. Thanks.

Some menu items in Excel have little icons next to the (for example File > Save has a little old fashined floppy disc image). the FaceID allows you to add a little image to your menu from the set of several thousand provided with Excel.

More is explained here :
http://spreadsheetpage.com/index.php...andbar_images/

You can download a FacieID browser from here :
http://skp.mvps.org/faceid.htm

Why did I comment it out? 'Cos it was late, and I couldn't be bothered looking for a suitable image (not all numbers have an image assigned, so it could have been blank) - I just left it in to show it was ine if the options that can be used in this sort of code.

HTH

DominicB