Turn On/Off Add-In from some toolbar Menu

excel 2003  vba

I have code here that creates a menu/submenu item from the "Tools" menu option in Excel.


BUT its only created when an Add-In  is  Selected or Activated !

---------------------------------------------------------------------------------------------------
What I need:

I need someway via a toolbar to activate or deactivate an Add-In.


Thanks
fordraiders

Private Sub Workbook_Open()
    
    Set xlApp = Excel.Application

'''''''''''''''''''''''''''''''''''''''''''''''
' Workbook_Open
' Create a submenu on the Tools menu. The
' submenu has two controls on it.
'''''''''''''''''''''''''''''''''''''''''''''''
Dim ToolsMenu As Office.CommandBarControl
Dim ToolsMenuItem As Office.CommandBarControl
Dim ToolsMenuControl As Office.CommandBarControl

'''''''''''''''''''''''''''''''''''''''''''''''
' First delete any of our controls that
' may not have been properly deleted previously.
'''''''''''''''''''''''''''''''''''''''''''''''
DeleteControls

''''''''''''''''''''''''''''''''''''''''''''''
' Get a reference to the Tools menu.
''''''''''''''''''''''''''''''''''''''''''''''
Set ToolsMenu = Application.CommandBars.FindControl(ID:=C_TOOLS_MENU_ID)
If ToolsMenu Is Nothing Then
    MsgBox "Unable to access Tools menu.", vbOKOnly
    Exit Sub
End If

''''''''''''''''''''''''''''''''''''''''''''''
' Create a item on the Tools menu.
''''''''''''''''''''''''''''''''''''''''''''''
Set ToolsMenuItem = ToolsMenu.Controls.Add(Type:=msoControlPopup, temporary:=True)
If ToolsMenuItem Is Nothing Then
    MsgBox "Unable to add item to the Tools menu.", vbOKOnly
    Exit Sub
End If

With ToolsMenuItem
    .Caption = "&Enterprise_Menu_Item"
    .BeginGroup = True
    .Tag = C_TAG
End With

''''''''''''''''''''''''''''''''''''''''''''''
' Create the first control on the new item
' in the Tools menu.
''''''''''''''''''''''''''''''''''''''''''''''
Set ToolsMenuControl = ToolsMenuItem.Controls.Add(Type:=msoControlButton, temporary:=True)
If ToolsMenuControl Is Nothing Then
    MsgBox "Unable to add item to Tools menu item.", vbOKOnly
    Exit Sub
End If

With ToolsMenuControl
    ''''''''''''''''''''''''''''''''''''
    ' Set the display caption and the
    ' procedure to run when clicked.
    ''''''''''''''''''''''''''''''''''''
    .Caption = "&Excel Xref Enterprise"
    .OnAction = "'" & ThisWorkbook.Name & "'!MyRange"
    .Tag = C_TAG
End With

''''''''''''''''''''''''''''''''''''''''''''''
' Create the second control on the new item
' in the Tools menu.
''''''''''''''''''''''''''''''''''''''''''''''
'Set ToolsMenuControl = ToolsMenuItem.Controls.Add(Type:=msoControlButton, temporary:=True)
'If ToolsMenuControl Is Nothing Then
'    MsgBox "Unable to add item to Tools menu item.", vbOKOnly
'    Exit Sub
'End If

'With ToolsMenuControl
'    ''''''''''''''''''''''''''''''''''''
'    ' Set the display caption and the
'    ' procedure to run when clicked.
'    ''''''''''''''''''''''''''''''''''''
'    .Caption = "&Click Me Two"
'    .OnAction = "'" & ThisWorkbook.Name & "'!MacroToRunTwo"
'    .Tag = C_TAG
'End With


End Sub


Private Sub DeleteControls()
''''''''''''''''''''''''''''''''''''
' Delete controls whose Tag is
' equal to C_TAG.
''''''''''''''''''''''''''''''''''''
Dim Ctrl As Office.CommandBarControl

'On Error Resume Next
Set Ctrl = Application.CommandBars.FindControl(Tag:=C_TAG)

Do Until Ctrl Is Nothing
    Ctrl.Delete
    Set Ctrl = Application.CommandBars.FindControl(Tag:=C_TAG)
Loop

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ThisWorkbook Code Module
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Select allOpen in new window