Remove Command Bar when excel file closes
 

I'm having some trouble with a command bar.I have an auto open for a
command bar and that works great. However, if I close the .xls file
and not the whole program of Excel the command bar stays and if I re-
open the file a new open appears. Most of the time I have three .xls
files open and I open my macro file here and there but by doing that
it causes me to have like 5 command bars of the same thing. Is there a
way to get rid of the command bar when I close only the .xls file not
Excel the program


Option Explicit

Private SatlogMenu As CommandBarControl

Public Sub Auto_open()

Dim MainCommandBar As CommandBar
' Workbook Menu
Set MainCommandBar = Application.CommandBars("Worksheet Menu Bar")

Set SatlogMenu = MainCommandBar.Controls.Add(Type:=msoControlPopup,
Temporary:=True)

With SatlogMenu
.Visible = True
.Caption = "Satlog"

End With

AddMenuItem "Live Data", "GetFile"
AddMenuItem "Target", "Goal"
AddMenuItem "Input to Ctg Sheet", "Sendctgsheet"
AddMenuItem "Get Old Data", "GetFileManually"
' Chart Menu
Set MainCommandBar = Application.CommandBars("Chart Menu Bar")

Set SatlogMenu = MainCommandBar.Controls.Add(Type:=msoControlPopup,
Temporary:=True)

With SatlogMenu
.Visible = True
.Caption = "Satlog"

End With

AddMenuItem "New Satlog Plot", "NewSatlogChart"
AddMenuItem "Add Satlog to This Plot", "SatlogToChart"

End Sub

Private Sub AddMenuItem(ItemName, ProgramName)

Dim NewMenuItem As CommandBarControl

Set NewMenuItem = SatlogMenu.Controls.Add(Type:=msoControlButton)

NewMenuItem.Caption = ItemName
NewMenuItem.OnAction = ProgramName

End Sub

Remove Command Bar when excel file closes
 

Use the BeforeClose event of the ThisWorkbook module. In the VBE,
locate your workbook and open the ThisWorkbook module. Place in this
code. Now, whenever the spreadsheet closes it will delete the menu
named Satlog.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Worksheet Menu
Bar").Controls("Satlog").Delete
End Sub
wrote:
I'm having some trouble with a command bar.I have an auto open for a
command bar and that works great. However, if I close the .xls file
and not the whole program of Excel the command bar stays and if I re-
open the file a new open appears. Most of the time I have three .xls
files open and I open my macro file here and there but by doing that
it causes me to have like 5 command bars of the same thing. Is there a
way to get rid of the command bar when I close only the .xls file not
Excel the program


Option Explicit

Private SatlogMenu As CommandBarControl

Public Sub Auto_open()

Dim MainCommandBar As CommandBar
' Workbook Menu
Set MainCommandBar = Application.CommandBars("Worksheet Menu Bar")

Set SatlogMenu = MainCommandBar.Controls.Add(Type:=msoControlPopup,
Temporary:=True)

With SatlogMenu
.Visible = True
.Caption = "Satlog"

End With

AddMenuItem "Live Data", "GetFile"
AddMenuItem "Target", "Goal"
AddMenuItem "Input to Ctg Sheet", "Sendctgsheet"
AddMenuItem "Get Old Data", "GetFileManually"
' Chart Menu
Set MainCommandBar = Application.CommandBars("Chart Menu Bar")

Set SatlogMenu = MainCommandBar.Controls.Add(Type:=msoControlPopup,
Temporary:=True)

With SatlogMenu
.Visible = True
.Caption = "Satlog"

End With

AddMenuItem "New Satlog Plot", "NewSatlogChart"
AddMenuItem "Add Satlog to This Plot", "SatlogToChart"

End Sub

Private Sub AddMenuItem(ItemName, ProgramName)

Dim NewMenuItem As CommandBarControl

Set NewMenuItem = SatlogMenu.Controls.Add(Type:=msoControlButton)

NewMenuItem.Caption = ItemName
NewMenuItem.OnAction = ProgramName

End Sub