Excel 2003 & 2007.
Is it possible to check for visible toolbars in the Workbook_Open event and
have them all close.
Then in the Workbook_BeforeClose event re-open the same toolbars?
Sandy
I did the same thing with mine...here's my coding (change it to your
liking). It closes sheets tabs, row and column headings, and all menu
bar items except for 'File' and 'Help'.
Option Base 1
Dim MoveAfterReturn As Boolean
Dim MoveAfterReturnDirection As XlDirection
Dim CBvisible() As Boolean
Private Sub Workbook_Open()
Dim i As Integer
'Hide all commandbars, including formula bar, but not Worksheet Menu
Bar
With Application
ReDim CBvisible(.CommandBars.Count)
For i = 1 To .CommandBars.Count
CBvisible(i) = .CommandBars(i).Visible 'save original
visibility state
If .CommandBars(i).Name <> "Worksheet Menu Bar" Then
If .CommandBars(i).Visible Then .CommandBars(i).Visible =
False
End If
Next i
.DisplayFormulaBar = False
With .CommandBars("Worksheet Menu Bar")
For i = 1 To .Controls.Count
Select Case .Controls(i).Caption
Case "&File", "&Help"
Case Else
.Controls(i).Visible = False
End Select
Next i
End With
'save current settings so they can be restored later,
'then set enter key to move down
MoveAfterReturn = Application.MoveAfterReturn
MoveAfterReturnDirection = Application.MoveAfterReturnDirection
.MoveAfterReturn = True
.MoveAfterReturnDirection = xlToRight
End With
'Turn off row and column headings
ActiveWindow.DisplayHeadings = False
Sheets("Lists").Range("I2").Value = ""
Sheets("Home").Select
PermitTrackerSplash.Show
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Integer
'Unhide all commandbars, including formula bar, but not Worksheet
Menu Bar
With Application
For i = 1 To .CommandBars.Count
If .CommandBars(i).Visible <> CBvisible(i) Then
.CommandBars(i).Visible = CBvisible(i)
End If
Next i
.DisplayFormulaBar = True
With .CommandBars("Worksheet Menu Bar")
For i = 1 To .Controls.Count
.Controls(i).Visible = True
Next i
End With
'restore move-after-enter original settings
.MoveAfterReturn = MoveAfterReturn
.MoveAfterReturnDirection = MoveAfterReturnDirection
End With
'Turn on row and column headings
ActiveWindow.DisplayHeadings = True
End Sub