ExcelBanter - 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