Toggle Toolbars in Excel2003
 

I am new to this group; however, I have used many of the suggestions
given. I am trying to toggle all toolbars in Excel. The premise is when
the application opens, an array is created with all of the visible
toolbars. I then close all of these toolbars. When the application
closes, all of the "once visible" toolbars are opened again. I even
found some code on the Microsoft site to do this. The problem is that
it doesn't work on ALL toolbars. For example, Borders, Control Toolbox,
External Data, etc toolboxs will not toggle with the rest. Has anyone
else come across this? Any help would be greatly appreciated.

-William

Toggle Toolbars in Excel2003
 

This should do it

Option Explicit

Private mFormulaBar

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = mFormulaBar
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
I am new to this group; however, I have used many of the suggestions
given. I am trying to toggle all toolbars in Excel. The premise is when
the application opens, an array is created with all of the visible
toolbars. I then close all of these toolbars. When the application
closes, all of the "once visible" toolbars are opened again. I even
found some code on the Microsoft site to do this. The problem is that
it doesn't work on ALL toolbars. For example, Borders, Control Toolbox,
External Data, etc toolboxs will not toggle with the rest. Has anyone
else come across this? Any help would be greatly appreciated.

-William

Toggle Toolbars in Excel2003
 

Thanks so much for the reply. It works great! Can you give a quick
explaination as to what is going on in the code with "mFormulaBar"? I
am not sure how that is working. I am sure that it is something
simple...

Thanks again,
William Boswell

Toggle Toolbars in Excel2003
 

Thanks so much for the reply. It works great! Can you give a quick
explaination as to what is going on in the code with "mFormulaBar"? I
am not sure how that is working. I am sure that it is something
simple...

Thanks again,
William Boswell