Hide all Excel tool/command bars except mine and reload on close

[Matt Jensen]

Howdy
Hopefully the subject line says it all, but I'm wondering if it's at all
possible to, when my workbook is opened or activated, hide all toolbars
except the one created in my workbook, and then on close of my workbook or
deactivation of it, to show all previously visible command bars?
Obviously I don't want to muck up any user's existing toolbar setup, so is
it possible to do what I want with this in mind too?
Anyone got some sample code?
Thanks
Matt

 

[Bob Phillips]

Hi Matt,

Here are a couple of routines. Just change Standard for your toolbar name.

Dim colCBs As Collection

Sub HideEm()
Dim oCB As CommandBar

Set colCBs = New Collection
For Each oCB In Application.CommandBars
If oCB.Name <> "Standard" Then
If oCB.Visible Then
colCBs.Add oCB.Name
If oCB.Name = "Worksheet Menu Bar" Then
oCB.Enabled = False
Else
oCB.Visible = False
End If
End If
End If
Next oCB

End Sub


Sub ShowEm()
Dim i As Long

For i = 1 To colCBs.Count
If colCBs.Item(i) = "Worksheet Menu Bar" Then
Application.CommandBars(colCBs.Item(i)).Enabled = True
Else
Application.CommandBars(colCBs.Item(i)).Visible = True
End If
Next i

End Sub

--

HTH

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

 

[Matt Jensen]

Cheers Bob
Matt

Hi Matt,

Here are a couple of routines. Just change Standard for your toolbar name.

Dim colCBs As Collection

Sub HideEm()
Dim oCB As CommandBar

Set colCBs = New Collection
For Each oCB In Application.CommandBars
If oCB.Name <> "Standard" Then
If oCB.Visible Then
colCBs.Add oCB.Name
If oCB.Name = "Worksheet Menu Bar" Then
oCB.Enabled = False
Else
oCB.Visible = False
End If
End If
End If
Next oCB

End Sub


Sub ShowEm()
Dim i As Long

For i = 1 To colCBs.Count
If colCBs.Item(i) = "Worksheet Menu Bar" Then
Application.CommandBars(colCBs.Item(i)).Enabled = True
Else
Application.CommandBars(colCBs.Item(i)).Visible = True
End If
Next i

End Sub

--

HTH

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