Close all open toolbars
 

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

Close all open toolbars
 

On Jan 26, 7:51*am, "Sandy" wrote:
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

Close all open toolbars
 

Sandy,
in xl2003 i use following approach which stores visible toolbars in registry
and then hides all but worksheet menu bar.
When you restore, users toolbar original settings are maintained.

Hope useful.

Sub HideBars(state)
Dim cbar As CommandBar

On Error Resume Next

For Each cbar In Application.CommandBars

If state = xlOn Then

SaveSetting AppName:= _
"myapp", section:="CommandBars", _
key:=cbar.Name, Setting:=cbar.Visible

If cbar.Type < 1 Then cbar.Visible = False

Else

cbar.Visible = _
GetSetting(AppName:="myapp", section:="CommandBars", _
key:=cbar.Name)

End If
Next

On Error GoTo 0

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
HideBars (xlOff)
End Sub

Private Sub Workbook_Open()
HideBars (xlOn)
End Sub

--
jb


"Sandy" wrote:

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

Close all open toolbars
 

Many thanks to you all - fyi I used the following referred from both
workbook_open and workbook_beforeclose.

Sub HideBars(state)
Dim cbar As CommandBar

On Error Resume Next

For Each cbar In Application.CommandBars

If state = xlOn Then

SaveSetting AppName:="myapp", section:="CommandBars",
key:=cbar.Name, Setting:=cbar.Visible
cbar.Visible = False
Application.CommandBars("Worksheet Menu Bar").Enabled = False

Else

cbar.Visible = GetSetting(AppName:="myapp",
section:="CommandBars", key:=cbar.Name)
Application.CommandBars("Worksheet Menu Bar").Enabled = True
End If
Next

On Error GoTo 0

End Sub


"Sandy" wrote in message
...
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