Home |
Search |
Today's Posts |
#1
|
|||
|
|||
on exit macro
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 |
#2
|
|||
|
|||
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" wrote in message oups.com... 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 |
#3
|
|||
|
|||
thank you again BoB
where would we be without the generous help that you all provide, much appreciated regards, Ditchy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
automatic macro update | Excel Worksheet Functions | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
Macro for multiple charts | Excel Worksheet Functions |