Creating an Excel menu bar using a macro 1

[Sech]

A while back I posted a thread asking for help on creating an Excel menu bar using a macro when a particular spreadsheet was opened. The replies were very helpful and I have now managed to do this using menu items that have macros under them. The code under Workbook Open is as follows:

'Create new menu bar
Set brMyMenu = Application.CommandBars.Add(Name:="MyMenu", Position:=msoBarTop, MenuBar:=True, Temporary:=True)
With brMyMenu
'Add menu
Set cbpMyMenu = .Controls.Add(Type:=msoControlPopup)
With cbpMyMenu
.Caption = "&Options"
'Add Set menu item
Set cbbMymenu = .Controls.Add(Type:=msoControlButton)
With cbbMymenu
.Caption = "&Main Menu"
.FaceId = 18
.OnAction = "GoToMainMenu"
End With
'Add Set menu item
Set cbbMymenu = .Controls.Add(Type:=msoControlButton)
With cbbMymenu
.Caption = "&Set"
.FaceId = 18
.OnAction = "ReportSet"
End With
'Add Unset menu item
Set cbbMymenu = .Controls.Add(Type:=msoControlButton)
With cbbMymenu
.Caption = "&Unset"
.FaceId = 18
.OnAction = "ReportUnset"
End With
End With
'Make new menu visible (replace standard menu)
.Visible = True
End With

'Make toolbars invisible
Application.CommandBars("Standard&quot.Visible = False
Application.CommandBars("Formatting&quot.Visible = False

This creates a menu bar with three options under a single menu - Main Menu, Set and Unset. These all work by running other macros when they are selected. But does anyone know how to create normal menu items in the menu bar e.g. Print, Save As etc? When customising a menu bar manually these normal Excel commands can be dragged in but how are they added if the menu bar is being created using code?

 

[Loomah]

Hi
This may not be the definitive answer but I've just tested the following by removing the "Open" command from the File menu manually then replacing it with this code. The ID for Open is 23. I only assume that this will work with custom toolbars.

CommandBars("file").Controls.Add ID:=23, before:=2

The following is a simple method of obtaining the relevant ids

Sub a()
Dim ctrl As CommandBarControl
For Each ctrl In CommandBars("file").Controls
Debug.Print ctrl.ID, ctrl.Caption
Next
End Sub

Hope this helps at least a little!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[bandit600]

Try this

http://www.microsoft.com/officedev/articles/faceid.htm

bandit600

 

[Sech]

Thanks very much for your help. I got the menu bar to work correctly, now I just need to add the correct Face IDs. I checked out the link Bandit but the option to download the Face ID viewer program doesnt work correctly so I am none the wiser. Is there any other way of getting the right Face ID's? For example Loomah how did you know that the Face ID for Open was 23. Did you detect it somehow?

 

[Sech]

Ok, got it, please disregard my last reply. The Face ID is the same as the ID for the control. Thanks once again for your help...