PDA

View Full Version : Solved: Hiding Command Toolbars



jammer6_9
04-17-2007, 05:29 AM
When a workbook activates I wanted all Toolbars disabled. Below code works but I want it in one statement only. Is there any way to write this command in just one statement?

Private Sub Workbook_Activate()
Application.CommandBars("Standard").Enabled = False
Application.CommandBars("Formatting").Enabled = False
Application.CommandBars("borders").Enabled = False
Application.CommandBars("Chart").Enabled = False
Application.CommandBars("Control Toolbox").Enabled = False
Application.CommandBars("Drawing").Enabled = False
Application.CommandBars("External Data").Enabled = False
Application.CommandBars("Forms").Enabled = False
Application.CommandBars("Formula Auditing").Enabled = False
Application.CommandBars("List").Enabled = False
Application.CommandBars("Pictures").Enabled = False
Application.CommandBars("PivotTable").Enabled = False
Application.CommandBars("Protection").Enabled = False
Application.CommandBars("Reviewing").Enabled = False
Application.CommandBars("Text To Speech").Enabled = False
Application.CommandBars("Visual basic").Enabled = False
Application.CommandBars("Watch Window").Enabled = False
Application.CommandBars("WordArt").Enabled = False
Application.CommandBars("CheckBoxes").Enabled = False
Application.CommandBars("Flow Connectors").Enabled = False
Application.CommandBars("Flow Shapes").Enabled = False
Application.CommandBars("Excel Add-in for Analysis Service").Enabled = False
Application.CommandBars("Custom 1").Enabled = False
Application.CommandBars("Custom 2").Enabled = False
End Sub

Bob Phillips
04-17-2007, 06:14 AM
Not one line, but this is what I use Private mFormulaBar Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = True Next oCB Application.DisplayFormulaBar = mFormulaBar End Sub Private Sub Workbook_Open() Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = False Next oCB mFormulaBar = Application.DisplayFormulaBar Application.DisplayFormulaBar = False End Sub

jammer6_9
04-17-2007, 06:29 AM
Thanks xld... Thats more than enough... 'Am just having a thoughts on whats the deifference of Workbook_Activate & Workbook_Deactivate to Workbook_Open & Workbook_Close

Should I use the Workbook_Activate or Workbook_Open and in vice-versa Deactivate to Close...