Hiding and restoring toolbars

In some cases, you may want to remove all the toolbars when a workbook is opened. It's only polite, however, to restore the toolbars when your application closes. In this section I present two procedures, both stored in the code window of the ThisWorkbook object.

The Workbook_Open procedure, available at this book's Web site, is executed when the workbook is opened. This procedure saves the names of all visible toolbars in column A of Sheet1 and then hides all the toolbars:

Private Sub Workbook_Open()

Dim TBarCount As Integer

Dim cbar As CommandBar


TBarCount = 0

For Each cbar In Application.CommandBars

If cbar.Type = msoBarTypeNormal Then

If cbar.Visible Then

TBarCount = TBarCount + 1

Sheets("Sheet1").Cells(TBarCount, 1) =


cbar.Visible = False

End If

End If

Next cbar

End Sub

The following procedure is executed before the workbook is closed. This routine loops through the toolbar names stored on Sheet1 and changes their Visible property to True:

0 0

Post a comment