Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.templates,microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Adding a menu item to Excel
Hi,
I just added an "add-in" to Excel. (Tools add-ins browse...) I can see it in the VB editor but not on the application's menu. On a (Windows 2000 machine at work - - the item (GPIB.XLA) showed up automatically. At home I tried on two XP machines - did not work. In ALL 3 computers the it is Excel 2002. Any advice? Thanks, Uzi, 1-13-08 |
#2
Posted to microsoft.public.excel.programming, microsoft.public.excel.templates,microsoft.public.excel.misc, microsoft.public.excel
|
|||
|
|||
Adding a menu item to Excel
On Jan 14, 1:46*am, "Uzi Barzilai" wrote:
Hi, I just added an "add-in" to Excel. (Tools add-ins browse...) I can see it in the VB editor but not on the application's menu. On a (Windows 2000 machine at work - - the item (GPIB.XLA) showed up automatically. At home I tried on two XP machines - did not work. In ALL 3 computers the it is Excel 2002. Any advice? Thanks, Uzi, 1-13-08 Hi When you load an add-in it is effectively opening a workbook you cannot see. You must use the WorkBook_Open event to put your menu on the menu bar (and workBook_Close to remove it). Private Sub Workbook_Open() Call Add_Menu End Sub This sub is in the ThisWorkBook code module in VBA. The Sub Add_Menu is what you use to create your menu. Something like: Public Sub Add_Menu() Dim cbWSMenuBar 'Variable name for main Worksheet Menu Bar Dim muCustom As CommandBarControl 'menu item on main Toolbar Dim iHelpIndex As Integer 'item number of Help menubar item Set cbWSMenuBar = CommandBars("Worksheet Menu Bar") 'If Excel crashed while last opened so that Before_Close() event didn't happen 'the Timetable menubar may still exist. So delete it just in case On Error Resume Next cbWSMenuBar.Controls("myMenu").Delete On Error GoTo 0 iHelpIndex = cbWSMenuBar.Controls("Help").Index Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, befo=iHelpIndex) With muCustom .Caption = "myMenu" With .Controls.Add(Type:=msoControlButton) .Caption = "Sub1" .OnAction = "myFirstSub" End With End with This code is in a normal code module. The above sub will add a menu item to the main toolbar called myMenu (next to Help), which will have a submenu item called Sub1. Note the Delete at the top which removes myMenu if it wasn't removed when Excel was last closed. You will also need to remove myMenu when you close Excel, or you will have 2 of them next time you open. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Remove_Menu End Sub Remove_Menu is in a normal code module and simply deletes the menu. Public Sub Remove_RegisterMenu() Dim cbWSMenuBar As CommandBar On Error Resume Next 'Incase it has already been deleted Set cbWSMenuBar = CommandBars("Worksheet Menu Bar") cbWSMenuBar.Controls("myMenu").Delete End Sub apologies if you know most of this already, but Add-In menus are a common question and I wanted to make the mail self contained. regards Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to delete item from Pivot graph(Excel) drop-down menu | Excel Discussion (Misc queries) | |||
Control item PRINT in menu FILE of Excel | Excel Discussion (Misc queries) | |||
Excel 2000 Custom Menu Item | Excel Discussion (Misc queries) | |||
Menu items added with menu item editor in older versions | Excel Discussion (Misc queries) | |||
How to remove an Excel Main Menu item inserted by .xla file | Excel Discussion (Misc queries) |