Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop dwn menu. Formula to count selection frm menu in anoth cell? | Excel Worksheet Functions | |||
filter dropdown menu so 2nd drop menu is customized | Excel Worksheet Functions | |||
Create Dropdown menu without using the Validation on the Data Menu | Excel Worksheet Functions | |||
Menu items added with menu item editor in older versions | Excel Discussion (Misc queries) | |||
Window's System Menu, can't remove menu | Excel Programming |