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,Befo=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
---
Message posted from
http://www.ExcelForum.com/