New Menu Name
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 -- Message posted from http://www.ExcelForum.com |
New Menu Name
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,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 -- Regards, Tom Ogilvy "pauluk " wrote in message ... 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 how to give the menu a name Dim CmdBar As CommandBar Set CmdBar = Application.CommandBars("Worksheet Menu Bar") CmdBar.Controls.Add --- Message posted from http://www.ExcelForum.com/ |
New Menu Name
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/ |
All times are GMT +1. The time now is 02:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com