Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel closes unexpectedly on print command causing a spooler.exe e | Excel Discussion (Misc queries) | |||
Excel 2003 File Closes In 2000 | Links and Linking in Excel | |||
canot remove command from FILE MENU | Excel Discussion (Misc queries) | |||
Remove code from all modules closes Excel | Excel Programming | |||
programmatically close help file before Excel closes? | Excel Programming |