Hiding toolbars

Posted by Phil on June 26, 2001 2:45 AM

Does Any1 know how to hide all of the users toolbars when they open an excel sheet an return them when they leave...?

Thanxs

Phil

Posted by Dax on June 26, 2001 4:26 AM

Phil,

If you place this code in the Workbook code module you should get what you want. You must also have a module with the line: -

Public CombarsList(0 To 100) As String

This macro doesn't hide the Worksheet Menu Bar (File, Edit...) because you can't hide it directly. However, you can replace it with your own custom menu.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim CBCounter As Long
For CBCounter = 0 To 100
If CombarsList(CBCounter) = "" Then
Exit For
Else
Application.CommandBars(CombarsList(CBCounter)).Visible = True
End If
Next
End Sub

Private Sub Workbook_Open()
Dim Combar As CommandBar, CBCounter As Long
For Each Combar In Application.CommandBars
If Combar.Visible = True And Combar.Name &LT;&GT; "Worksheet Menu Bar" Then
CombarsList(CBCounter) = Combar.Name
CBCounter = CBCounter + 1
Combar.Visible = False
End If
Next
End Sub

HTH,
Dax.