I have created an xla that adds a new menu to the worksheet menu bar at
startup, it allows me to access files. I can go into the Tools, addins menu
and add the xla and it appears on the command bar. but when I close down
excel and reopen it the addin does not appear.
I had excel 2000 up to a few days ago and my addin worked perfectly but now
that i have upgraded to excel 2002 i am having this problem. Can someone
please help? thanks if you can.

Here is my code.

Private Sub Workbook_AddinUninstall()
Call DeleteMenu
End Sub

Private Sub Workbook_AddinInstall()
Call AddMenus
End Sub
Public Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl


On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&Andy
Files").Delete



Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")


iHelpMenu = _
cbMainMenuBar.Controls("Data").Index


Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
befo=iHelpMenu)


cbcCutomMenu.Caption = "&Andy Files"

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Limit Monitor"
.OnAction = "OpenLimitMonitor"
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&My Work"
.OnAction = "OpenAndyWork"
End With


On Error GoTo 0
End Sub

Public Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&Andy
Files").Delete
On Error GoTo 0
End Sub

Just a tought - as this is something that has caught me
out before - you havent been using more than one copy of
Excel at a time have you. It appears that the last WExcel
to close will write out which Addins are loaded. So if
you close the one with the addin first, then another one -
your addin disappears...

First, your code worked ok for me in xl2002.

So I don't have a real good guess.

But what do you mean by: "the addin does not appear"?

Does this mean that the options on the toolbar don't appear or that the addin
name in the Tools|Addins dialog doesn't appear?

(Either way, I'm at a loss--since it worked fine for me.)

If you check the VBE, do you see your addin in the project explorer?

====
I've always used workbook_beforeclose and workbook_open for this type thing.
Maybe you could try it to see if that helps (but I don't know why it would!).




mranke wrote:

I have created an xla that adds a new menu to the worksheet menu bar at
startup, it allows me to access files. I can go into the Tools, addins menu
and add the xla and it appears on the command bar. but when I close down
excel and reopen it the addin does not appear.
I had excel 2000 up to a few days ago and my addin worked perfectly but now
that i have upgraded to excel 2002 i am having this problem. Can someone
please help? thanks if you can.

Here is my code.

Private Sub Workbook_AddinUninstall()
Call DeleteMenu
End Sub

Private Sub Workbook_AddinInstall()
Call AddMenus
End Sub
Public Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl


On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&Andy
Files").Delete



Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")


iHelpMenu = _
cbMainMenuBar.Controls("Data").Index


Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup,
befo=iHelpMenu)


cbcCutomMenu.Caption = "&Andy Files"

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Limit Monitor"
.OnAction = "OpenLimitMonitor"
End With
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&My Work"
.OnAction = "OpenAndyWork"
End With


On Error GoTo 0
End Sub

Public Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&Andy
Files").Delete
On Error GoTo 0
End Sub

--

Dave Peterson