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

Thread: Hiding toolbars, status bar etc.??

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all

    I am using the following code within sub workbook_open ()

    With Application
    .Caption = "Store Paperwork"
    .DisplayFormulaBar = False
    .DisplayStatusBar = False
    .DisplayScrollBars = False
    .CommandBars("worksheet menu bar").Enabled = False
    .CommandBars("chart menu bar").Enabled = False
    .DisplayFullScreen = True
    .CommandBars("full screen").Enabled = False
    End With
    With ActiveWorkbook
    .Windows(1).Caption = Empty
    .Windows(1).DisplayWorkbookTabs = False
    .Windows(1).DisplayHeadings = False
    .Windows(1).DisplayGridlines = False

    and it works fine for a split second until a sheet is selected at the end of the procedure and then some of them turn themselves back on.

    Anyone know why this is happening and how I can avoid it please? (this is really starting to bug me)

    TIA

    Dan.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My head is starting to hurt now with banging it against the monitor.

    Any ideas anyone PLEASE.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    With Application
    .Caption = "Store Paperwork"
    .DisplayFormulaBar = False
    .DisplayStatusBar = False
    .DisplayScrollBars = False
    .CommandBars("worksheet menu bar").Enabled = False
    .CommandBars("chart menu bar").Enabled = False
    .DisplayFullScreen = True
    .CommandBars("full screen").Enabled = False
    End With
    With ActiveWorkbook
    .Windows(1).Caption = Empty
    .Windows(1).DisplayWorkbookTabs = False
    .Windows(1).DisplayHeadings = False
    .Windows(1).DisplayGridlines = False

    and it works fine for a split second until a sheet is selected at the end of the procedure and then some of them turn themselves back on.

    Anyone know why this is happening and how I can avoid it please? (this is really starting to bug me)
    Dan,

    From the Excel help for DisplayGridlines and DisplayHeadings

    'This property affects only displayed headings'

    Not sure about the formula bar though.

    Why don't you select the sheet before running the code? By the way I like this code and may nick it!

    Regards,
    Dan

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dan

    I just realised I missed part of the code out so here is the full code. It should work but its not and I know its probably just something small Im missing.

    Sub screenset()
    Sheets("toolbars").Select
    Range("a1:a20").ClearContents
    Range("a1").Select
    For Each t In Application.Toolbars
    If t.Visible = True Then
    ActiveCell.Value = t.Name
    ActiveCell.Offset(1, 0).Select
    t.Visible = False
    End If
    Next t
    Range("a1").Select
    With Application
    .Caption = "Store Paperwork"
    .DisplayFormulaBar = False
    .DisplayStatusBar = False
    .DisplayScrollBars = False
    .CommandBars("worksheet menu bar").Enabled = False
    .CommandBars("chart menu bar").Enabled = False
    .DisplayFullScreen = True
    .CommandBars("full screen").Enabled = False
    End With
    With ActiveWorkbook
    .Windows(1).Caption = Empty
    .Windows(1).DisplayWorkbookTabs = False
    .Windows(1).DisplayHeadings = False
    .Windows(1).DisplayGridlines = False
    End With
    'Sheets("store paperwork").EnableSelection = xlUnlockedCells
    Sheets("store paperwork").Select
    Range("a1").Select
    End Sub

    If you use it, and get it working please let me know where I'm going wrong.

    Thanks

    Dan.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dan,

    How about this?

    Code:
    Sub screenset()
    Sheets("toolbars").Range("a1:a20").ClearContents
    
    r = 1
    Range("a1:a20").ClearContents
    Range("a1").Select
    For Each t In Application.Toolbars
        If t.Visible = True Then
            Sheets("toolbars").Cells(r, 1).Value = t.Name
            t.Visible = False
            r = r + 1
        End If
    Next t
    
    Sheets("store paperwork").EnableSelection = xlUnlockedCells
    Sheets("store paperwork").Activate
    Range("a1").Select
    
    With Application
        .Caption = "Store Paperwork"
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .DisplayScrollBars = False
        .CommandBars("worksheet menu bar").Enabled = False
        .CommandBars("chart menu bar").Enabled = False
        .DisplayFullScreen = True
        .CommandBars("full screen").Enabled = False
    End With
    With ActiveWorkbook
        .Windows(1).Caption = Empty
        .Windows(1).DisplayWorkbookTabs = False
        .Windows(1).DisplayHeadings = False
        .Windows(1).DisplayGridlines = False
    End With
    End Sub
    HTH,
    Dan

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dan

    I've worked out whats wrong with it.

    The application.displaysetting=false needs to go first in the procedure or for some reason it cancels out the toolbars bit.

    Now works fine for me. So if you want to use it do the above.

    Dan.

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
  •