Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Hiding Toolbars on Opening Workbook

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    California
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.)

    Code:
    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:

    Code:
    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •