I’m working in Excel and have written:

Private Sub Workbook_AddinInstall()

Dim cbFDS As CommandBar
Dim muAddIn As CommandBarControl
Dim iPosition As Integer

Set cbFDS = Application.CommandBars("Worksheet Menu Bar")

iPosition = cbFDS.Controls("Tools").Controls.Count
Set muAddIn = cbFDS.Controls("Tools").Controls.Add(Type:=msoControlButton, before:=iPosition)
muAddIn.Caption = "My Prog"
muAddIn.OnAction = "Main"
MsgBox "Install successful! Thank you!", vbInformation, "My Prog"

End Sub

2 Problems:

1) If I select My Prog from the tools menu whilst a workbook with a Sub Main() is the activeworkbook it runs this macro and not the one in my addin. Is there some way to explicitly reference Sub Main within my addin using the Onaction property for example?

2) If install the addin, close and reopen Excel ‘My Prog’ is missing from the Tools drop down. This occurs only when some other addins (nothing to do with my project) are installed on the machine – on a machine without these addins it works fine. To get the menu back I have to uncheck My Prog from Tools>Addins and then recheck it (i.e., reinstall the addin).

Thanks.