Close all open toolbars

[Sandy]

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

 

[gab1972]

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

 

[Ron de Bruin]

More info and a download on this page
http://www.rondebruin.nl/menuid.htm



'***************************************************************************
'how do I Disable all or almost all commandbars
'***************************************************************************


'All Command bars
'*****************

Sub Disable_Command_Bars_1()
'This will disable all Command bars
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
Cbar.Enabled = False
Next
End Sub


Sub Enable_Command_Bars_1()
'This will Enable all Command bars
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
Cbar.Enabled = True
Next
End Sub



'All BuiltIn Command bars
'*****************************************

Sub Disable_Command_Bars_2()
'This will disable all BuiltIn Command bars
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.BuiltIn = True Then
Cbar.Enabled = False
End If
Next
End Sub


Sub Enable_Command_Bars_2()
'This will Enable all BuiltIn Command bars
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.BuiltIn = True Then
Cbar.Enabled = True
End If
Next
End Sub



'All Command bars except the Worksheet Menu Bar
'*****************************************

Sub Disable_Command_Bars_3()
' This will disable all Command bars except
' the Worksheet Menu Bar
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.Name <> "Worksheet Menu Bar" Then
Cbar.Enabled = False
End If
Next
End Sub


Sub Enable_Command_Bars_3()
' This will Enable all Command bars except
' the Worksheet Menu Bar
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.Name <> "Worksheet Menu Bar" Then
Cbar.Enabled = True
End If
Next
End Sub

 

[john]

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

 

[Sandy]

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