Add Commandbar when Add-in is install

Hi all,
I have this code, it works really well if I put it in workbook_open() event.
But I need the commandbar active when user install the add-in file. and
delete it when the add-in is removed
Thanks for your help

Private Sub Workbook_Open()

Dim oCb As CommandBar
Dim oCtl As CommandBarButton

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Import
Text").Delete
On Error GoTo 0
Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtl.Caption = "Import Text"
oCtl.Style = msoButtonCaption
oCtl.OnAction = "ImportText"
End With
End Sub

That should be okay as is as it will load when the workbook opens, that is
when it is installed and when Excel starts.

You should delete it when the addin closes

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar") _
.Controls("Import Text").Delete
On Error GoTo 0
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"A.Q" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
> I have this code, it works really well if I put it in workbook_open()
event.
> But I need the commandbar active when user install the add-in file. and
> delete it when the add-in is removed
> Thanks for your help
>
> Private Sub Workbook_Open()
>
> Dim oCb As CommandBar
> Dim oCtl As CommandBarButton
>
> On Error Resume Next
> Application.CommandBars("Worksheet Menu Bar").Controls("Import
> Text").Delete
> On Error GoTo 0
> Set oCb = Application.CommandBars("Worksheet Menu Bar")
> With oCb
> Set oCtl = .Controls.Add( _
> Type:=msoControlButton, _
> temporary:=True)
> oCtl.Caption = "Import Text"
> oCtl.Style = msoButtonCaption
> oCtl.OnAction = "ImportText"
> End With
> End Sub