Sub HideAllToolBars()
Dim TB As CommandBar
Dim TBNum As Integer
Dim TBSheet As Worksheet
Set TBSheet = Sheets("TBSheet")
Application.ScreenUpdating = False
'Clear the sheet
TBSheet.Cells.Clear
'Hide all visible toolbars and restore
'their names
TBNum = 0
For Each TB In CommandBars
If TB.Type = msoBarTypeNormal Then
If TB.Visible Then
TBNum = TBNum + 1
TB.Visible = False
TBSheet.Cells(TBNum, 1) = TB.Name
End If
End If
Next TB
Application.ScreenUpdating = True
End Sub
Sub RestoreToolBars()
Dim TBSheet As Worksheet
Set TBSheet = Sheets("TBSheet")
Application.ScreenUpdating = False
'Unhide the previously displayed the toolbars
On Error Resume Next
For Each Cell In TBSheet.Range("A:A") _
.SpecialCells(xlCellTypeConstants)
CommandBars(cell.Value).Visible = True
Next cell
Application.ScreenUpdating = True
End Sub
P.S.
The HideAllToolBars procedure is called from
the Workbook_Open event handler and the
RestoreToolBars procedure is called from the
Workbook_BeforeClose event handler
James
_________________
This message was edited by James on 2002-05-04 18:01