Toolbar Problems

[Fan924]

They work well. That is not the problem. I have many sheets and each
has a unique toolbar. They open and close with the page using the
following sheet code.

Private Sub Worksheet_Deactivate()
Call NoBarPOT20x12
End Sub
Private Sub Worksheet_Activate()
Call BarPOT20x12
Application.Goto Range("A1"), Scroll:=True
End Sub

When I open a second sheet, I would like them to hide when the other
sheet is selected, Second, I need a better way to kill all of the
toolbars before closing. Is there a magical master command that will
close all my custom toolbars reliably. Now, I open other workbooks
with one of these toolbars still active. this happens too often. TIA.

 

[Chip Pearson]

The Worksheet_Deactivate and Worksheet_Activate event are tied only to
the worksheet in which the code resides. To get Activate and
Deactivate events for all sheets in the workbook, use the following in
the ThisWorkbook code module, not one of the Sheet modules.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
' code
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
' code
End Sub

In these procedures, the Sh parameter is a reference to the sheet
being activated or deactivated.

Also in the ThisWorkbook module, you can use

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' code
End Sub

to get an event notification that the workbook (though not necessarily
Excel itself) is being closed. In that event, delete your command
bars:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application.CommandBars
.Item("MyBar1").Delete
.Item("MyBar2").Delete
' and so on
End With
End Sub

When you create the command bars, you could store the names of the
command bars in a Colleciton object and then in BeforeClose, read
through that Collection and delete the command bars. E.g.,

When you create the commandbars, do something like

Dim Coll As New Collection
Sub CreateBars()
Dim CBar As Office.CommandBar
' Create CBar and its buttons
Coll.Add CBar.Name
' repeat for each comand bar
End Sub

Then, in ThisWorkbook,

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim S As String
For Each S In Coll
Application.CommandBars(S).Delete
Next S
End Sub

If you want to delete ALL custom command bars, regardless of how they
came into being, use

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim CBar As Office.CommandBar
For Each CBar In Application.CommandBars
If CBar.BuiltIn = False Then
CBar.Delete
End If
Next CBar
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Fan924]

Thanks Chip,
I am getting this error. It is an apparent problem with Excel 97

Run time error '-2147467259(80004005)':
Method 'Delete' of object 'CommandBar' failed.

 

[Fan924]

If you want to delete ALL custom command bars, regardless of how they

came into being, use

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim CBar As Office.CommandBar
    For Each CBar In Application.CommandBars
        If CBar.BuiltIn = False Then
            CBar.Delete
        End If
    Next CBar
End Sub

Thanks Chip,
I am getting this error. It is an apparent problem with Excel 97

Run time error '-2147467259(80004005)':
Method 'Delete' of object 'CommandBar' failed.