Hiding Toolbars on Opening Workbook

[paulw]

What code would I need to hide ALL the Excel Toolbars/Formula Bar/Status Bar etc on opening a workbook?

[Corticus]

Sub Auto_Open()

Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Chart").Visible = False
Application.CommandBars("Clipboard").Visible = False
Application.CommandBars("Control Toolbox").Visible = False
Application.CommandBars("Drawing").Visible = False
Application.CommandBars("Exit Design Mode").Visible = False
Application.CommandBars("External Data").Visible = False
Application.CommandBars("Forms").Visible = False
Application.CommandBars("Picture").Visible = False
Application.CommandBars("PivotTable").Visible = False
Application.CommandBars("PivotTable").Visible = False
Application.CommandBars("Stop Recording").Visible = False
Application.CommandBars("Visual Basic").Visible = False
Application.CommandBars("Visual Basic").Visible = False
Application.CommandBars("Web").Visible = False
Application.CommandBars("WordArt").Visible = False
End Sub

You might want to add an Auto_Close that undoes all this!!

[artslave]

Auto_Open is an older feature, which may not continue to be supported. For newer versions, you might want the code in a Workbook Open event, stored in your ThisWorkbook module. (A shortcut to get to it is by right-clicking the "Excel" logo at the far left end of your menu bar -- next to "File" -- and choosing View Code; otherwise, choose "ThisWorkbook" from the Microsoft Excel Objects folder in your project in Visual Basic Editor.)

Sub Workbook_Open()
    'thanks to Corticus!
    With Application
        .CommandBars("Standard").Visible = False 
        .CommandBars("Formatting").Visible = False 
        .CommandBars("Chart").Visible = False 
        .CommandBars("Clipboard").Visible = False 
        .CommandBars("Control Toolbox").Visible = False 
        .CommandBars("Drawing").Visible = False 
        .CommandBars("Exit Design Mode").Visible = False 
        .CommandBars("External Data").Visible = False 
        .CommandBars("Forms").Visible = False 
        .CommandBars("Picture").Visible = False 
        .CommandBars("PivotTable").Visible = False 
        .CommandBars("PivotTable").Visible = False 
        .CommandBars("Stop Recording").Visible = False 
        .CommandBars("Visual Basic").Visible = False 
        .CommandBars("Visual Basic").Visible = False 
        .CommandBars("Web").Visible = False 
        .CommandBars("WordArt").Visible = False 

'and, to turn off other bars:
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .ActiveWindow.DisplayWorkbookTabs = False

'just for fun, if you want to change the
'Title Bar from "Microsoft Excel" to "Whatever":
        .Caption = "Whatever"
    End With
End Sub

To turn it all back on when you're done, enter in same module:

Sub Workbook_BeforeClose(Cancel As Boolean)
    (same macro, change all "False" to "True", and set
    .Caption = Empty)          
End Sub

I'm sure there's a way to code "For Each command bar", set to "False" so you don't have to choose them all by name, but since I'm not 100% certain of the syntax off the top of my head, I'll let someone else clarify that.

Hope that helps,
Catherine

[ This Message was edited by: artslave on 2002-05-09 09:22 ]