Enable toolbars at close

[Pete]

I created a macro rich file that when it is opened all the
active toolbars that the user has on there screen are
turned off. What I want to do is be able to make them
active again once the user closes the spreadsheet. This is
the method I am using right now.
............................
Option Explicit
Public ToolbarArray(20) As String

Sub HideAllToolbars()
Dim TBVar As Variant
Dim Counter As Integer
Counter = 1
For Each TBVar In Application.Toolbars
If TBVar.Visible = True Then
ToolbarArray(Counter) = TBVar.Name
TBVar.Visible = False
Counter = Counter + 1
End If
Next
End Sub

Sub RestoreToolbars()
Dim TBVar As Variant
For Each TBVar In ToolbarArray
If (TBVar <> "") Then
Application.Toolbars(TBVar).Visible = True
End If
Next
End Sub
...........................
This works fine if the user only opens and closes the
program with running any of the macros in the spreadsheet,
but once any macro is performed the memory of what
toolbars are shut off is wiped out and the restore Toolbar
sub does nothing when initiated.

Is there a way around this? If I have to I am willing to
recall each toolbar indivisually, but I am not sure how to
do that?

Any help is appreciated.
Pete

 

[Bob Phillips]

Application.Toolbars?

Try

Application.Commandbars

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[James]

Bob I don't follow your instruction. I also have a similar
situation where I want to disable certain toolbars when my
spreadsheet opens.

(ie. Standard, formatting, reviewing, visual basic....)

How do you turn these off and on via code at will?
Please advise.
James

 

[Tom Ogilvy]

CommandBars("Formatting").Visible = False

to get a list

rw = 1
for each cbar in Application.Commandbars
cells(rw,1).Value = cbar.name
rw = rw + 1
Next


For the formula and/or statusbar

Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True

 

[Danny]

Try This:

Sub DIS_ENAB_CB()
Dim CB As CommandBar
For Each CB In Application.CommandBars
CB.Visible 'Enable = True 'False
Next CB
End Sub