C
carlos_ray86
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
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