Here's a little improvement on Aaron Blood's save and restore toolbars - http://www.ozgrid.com/forum/showthread.php?t=22076
This (as it is) will make 'visible = false' every toolbar, the status bar, formula bar, headings, scrollbars, the worksheet menu bar, and workbook tabs (basically everything you can remove) and then restores the default settings either when A) you close the workbook, or B) you switch to another workbook. It will remove all options again when you switch back.
If you want, add two buttons on a worksheet and link them to the macros turn_on and turn_off and it will remember which one was last clicked and return those settings when you switch back from another excel window. (So if the user makes the toolbars visible, switches to another window and then switches back, they wont be hidden again)
In a module:
Option Explicit
Public arr(1 To 117) As Integer
Public varOptionson As Integer
Sub define_toolbars()
Dim x As Integer
For x = 1 To 117
If Application.CommandBars(x).Visible = True Then
arr(x) = 1
End If
Next x
End Sub
Sub turn_off()
Application.CommandBars(1).Enabled = False
Application.DisplayStatusBar = False
Application.DisplayFormulaBar = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.DisplayVerticalScrollBar = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayWorkbookTabs = False
Dim x As Integer
For x = 2 To 117
If arr(x) = 1 Then
Application.CommandBars(x).Visible = False
End If
Next x
varOptionson = 0
End Sub
Sub turn_on()
Application.CommandBars(1).Enabled = True
Application.DisplayStatusBar = True
Application.DisplayFormulaBar = True
ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayWorkbookTabs = True
Dim x As Integer
For x = 2 To 117
If arr(x) = 1 Then
Application.CommandBars(x).Enabled = True
Application.CommandBars(x).Visible = True
End If
Next x
varOptionson = 1
End Sub
Sub switch_to()
If varOptionson = 0 Then
Application.CommandBars(1).Enabled = True
Application.DisplayStatusBar = True
Application.DisplayFormulaBar = True
Dim x As Integer
For x = 2 To 117
If arr(x) = 1 Then
Application.CommandBars(x).Enabled = True
Application.CommandBars(x).Visible = True
End If
Next x
End If
End Sub
Sub switch_back()
If varOptionson = 0 Then
Application.CommandBars(1).Enabled = False
Application.DisplayStatusBar = False
Application.DisplayFormulaBar = False
Dim x As Integer
For x = 2 To 117
If arr(x) = 1 Then
Application.CommandBars(x).Visible = False
End If
Next x
End If
End SubIn 'Thisworkbook' module:
Private Sub Workbook_Activate()
Application.Run "switch_back" ' turn off options
End Sub
Private Sub Workbook_Deactivate()
Application.Run "switch_to" ' turn on options
End Sub
Private Sub Workbook_Open()
Application.Run "define_toolbars"
' define current settings and save so can restore on exit, only run once
Application.Run "turn_off"
' turn off options
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Run "turn_on" ' reset user commandbars and options
End Sub