on exit macro

[ditchy]

Hello there
would someone be able to help me with a way to reinstate Toolbars
on exit.
The problem I have at the moment is when I remove the toolbars and
close, the toolbars are gone from all workbooks. I need the toolbars to
be unhidden on exit.
Macro below, help would be appreciated
Thanks Ditchy


Option Base 1
Public VisibleState(200) As Variant



Sub RemoveToolbars()
Dim a As Integer:
Application.CommandBars("Worksheet Menu Bar").Enabled = False
For a = 1 To Application.CommandBars.Count
VisibleState(a) = Application.CommandBars(a).Visible
If CommandBars(a).Visible = True Then CommandBars(a).Visible = False
Next a
End Sub





Sub ReinstateToolbars()
Dim a As Integer
Application.CommandBars("Worksheet Menu Bar").Enabled = True
On Error Resume Next
For a = 1 To Application.CommandBars.Count
Application.CommandBars(a).Visible = VisibleState(a)
Next a
End Sub

 

[Bob Phillips]

I have modified your routine as well

Option Explicit

Option Base 1
Public VisibleState()

Sub RemoveToolbars()
Dim a As Long
Dim cCBs As Long
Application.CommandBars("Worksheet Menu Bar").Enabled = False
ReDim VisibleState(1)
cCBs = 1
For a = 1 To Application.CommandBars.Count
If CommandBars(a).Visible = True Then
ReDim Preserve VisibleState(cCBs)
VisibleState(cCBs) = Application.CommandBars(a).Name
CommandBars(a).Visible = False
cCBs = cCBs + 1
End If
Next a
End Sub

Sub AddToolbars()
Dim a As Integer:
Application.CommandBars("Worksheet Menu Bar").Enabled = True
For a = LBound(VisibleState) To UBound(VisibleState)
Application.CommandBars(VisibleState(a)).Visible = True
Next a
End Sub


--

HTH

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

 

[ditchy]

thank you again BoB
where would we be without the generous help that you all provide, much
appreciated
regards, Ditchy