Excel macro to create Menu

[manekankit]

Hi all,

Currently I am am using below codes to create excel menu in Excel 2013.

1. It is creating Add-ins Menu and under Add-ins menu it is creating sub menu called MyMenu with 4 command buttons

However I want this menu in different way.

2. I want to create this menu in line with other menu after the end of default excel menus (just like home, insert etc.) and not under add-ins menu.

3. Further it will be easier if i can write Menu name, level, sub-level, face id etc. in any one sheet.

Request you to help me modify code.

Also in case you are having any other code or file, please share.
I tried searching but could not find exactly what I am looking for.



-----------------------------------Code------------------------------------------------
Sub Workbook_Open()
Dim cmbBar As CommandBar
Dim cmbControl As CommandBarControl


Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) 'adds a menu item to the Menu Bar
With cmbControl
.Caption = "&MyMenu" 'names the menu item
With .Controls.Add(Type:=msoControlButton) 'adds a dropdown button to the menu item
.Caption = "Caption1" 'adds a description to the menu item
.OnAction = "Code1" 'runs the specified macro
.FaceId = 246 'assigns an icon to the dropdown
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Caption2"
.OnAction = "Code2"
.FaceId = 246
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Caption3"
.OnAction = "Code3"
.FaceId = 246
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Caption4"
.OnAction = "Code4"
.FaceId = 246
End With
End With
End Sub
-----------------------------------Code------------------------------------------------

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next 'in case the menu item has already been deleted
Application.CommandBars("Worksheet Menu Bar").Controls("MyMenu").Delete 'delete the menu item
End Sub
-----------------------------------Code------------------------------------------------