New Menu Name

[pauluk]

Hi,

Can anyone tell me how i can create a new menu with VBA.

I have this code so far which places a menu but i can't figure out ho
to give the menu a name

Dim CmdBar As CommandBar
Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
CmdBar.Controls.Ad

 

[Tom Ogilvy]

Here is some sample code that Bob Phillips has previously posted. It adds a
menu before the Help menu.


Sub AddMenu()

Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl

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

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")

iHelpMenu = cbMainMenuBar.Controls("Help").Index

Set cbcCustomMenu = cbMainMenuBar.Controls. _
Add(Type:=msoControlPopup,Before:=iHelpMenu)

cbcCustomMenu.Caption = "MyMenu"

With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "item 1"
.OnAction = "macro1"
End With
With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "item 2"
.OnAction = "macro2"
End With
With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "item 3"
.OnAction = "macro3"
End With

End Sub

 

[pauluk]

If you are using this code for different work books then i would
consider creating an addin instead of a macro.

It is the same as creating a macro just that you do it in a blank xls
flile then save the file as and addin and place into the addins
folder.

so your addin code would look like

Sub Auto_Open()
Application.Run "Name you saved the addin as.XLA!AddMenu"
End Sub

Sub AddMenu()
'<===== Main menu build ======>
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl

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

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")

iHelpMenu = cbMainMenuBar.Controls("Help").Index

Set cbcCustomMenu = cbMainMenuBar.Controls. _
Add(Type:=msoControlPopup,Before:=iHelpMenu)

cbcCustomMenu.Caption = "MyMenu"

With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
Caption = "item 1"
OnAction = "macro1"
End With
With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
Caption = "item 2"
OnAction = "macro2"
End With
With cbcCustomMenu.Controls.Add(Type:=msoControlButton)
Caption = "item 3"
OnAction = "macro3"
End With

End Sub
Sub Macro1()
<======Customer 1 ========>

sheets("Customer1").select
End sub