Addin Uninstalls itself everytime excel is closed

[PennStateBrian]

Hey everyone,

Have an issue that I think should be simple enough to fix. I have this addin (code below) that I want to be installed everytime I open excel, so that I have the menu item at my disposal. It seems however, whenever I close and reopen excel, the menu item is no longer there, but if I go to tools -> addins, it's still checked. So I have to uncheck it, then click ok, then go back to tools-> addins, reselect it, click ok. And THEN it shows up. Could I please get some guidance. Thanks.

Option Explicit
Dim objHelpMenuItem As CommandBarControl

Private Sub Workbook_AddinInstall()

Dim lngPos As Long
Dim objHelpMenu As CommandBar

Dim objExcelAbout As CommandBarControl

Set objHelpMenu = Application.CommandBars("Tools")

Set objExcelAbout = objHelpMenu.Controls("Macro")
If Not objExcelAbout Is Nothing Then
lngPos = objExcelAbout.Index
Else
lngPos = objHelpMenu.Controls.Count
End If

Set objHelpMenuItem = objHelpMenu.Controls.Add(msoControlButton, 1, , lngPos, True)

objHelpMenuItem.Caption = "Match and Sort..."
objHelpMenuItem.BeginGroup = True
objHelpMenuItem.OnAction = "sortData"


End Sub

Private Sub Workbook_AddinUninstall()

On Error Resume Next
objHelpMenuItem.Delete
On Error GoTo 0

End Sub

 

[RoryA]

Set objHelpMenuItem = objHelpMenu.Controls.Add(msoControlButton, 1, , lngPos, True)

The True argument makes the control temporary, which is not what you want. Change it to False.

 

[PennStateBrian]

Perfect. Thank You