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

Thread: Hide toolbars

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

    Default

    Will this line hide all the toolbars, if not can u give me 1 that will.

    Toolbars("Standard").Visible = False

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well if it does you may want to consider something in Workbook_BeforeClose =True

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

    Default

    i will do that, but first i need a formula that will hide all the toolbars. The formula i gave doesnt do that.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub HideAllToolBars()
    Dim TB As CommandBar
    Dim TBNum As Integer
    Dim TBSheet As Worksheet
    Set TBSheet = Sheets("TBSheet")
    Application.ScreenUpdating = False

    'Clear the sheet
    TBSheet.Cells.Clear

    'Hide all visible toolbars and restore
    'their names
    TBNum = 0
    For Each TB In CommandBars
    If TB.Type = msoBarTypeNormal Then
    If TB.Visible Then
    TBNum = TBNum + 1
    TB.Visible = False
    TBSheet.Cells(TBNum, 1) = TB.Name
    End If
    End If
    Next TB
    Application.ScreenUpdating = True
    End Sub

    Sub RestoreToolBars()
    Dim TBSheet As Worksheet
    Set TBSheet = Sheets("TBSheet")
    Application.ScreenUpdating = False

    'Unhide the previously displayed the toolbars
    On Error Resume Next
    For Each Cell In TBSheet.Range("A:A") _
    .SpecialCells(xlCellTypeConstants)
    CommandBars(cell.Value).Visible = True
    Next cell
    Application.ScreenUpdating = True
    End Sub

    P.S.
    The HideAllToolBars procedure is called from
    the Workbook_Open event handler and the
    RestoreToolBars procedure is called from the
    Workbook_BeforeClose event handler

    James

    _________________


    [ This Message was edited by: James on 2002-05-04 18:01 ]

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    it looks so compicated,
    what exactly does this do

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It hides your Tool Bars when the Workbook is opened and retores them when the Workbook is closed.

    I may be wrong, but isn't that what you wanted to do? Your post said "HideAllToolBars"

    James

    _________________


    [ This Message was edited by: James on 2002-05-04 20:17 ]

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Had the same problem myself, try this

    Put this into the workbook Code
    You can play around with the alternatives

    Private Sub Workbook_Activate()
    Application.CommandBars("Standard").Visible = False
    Application.CommandBars("Formatting").Visible = False
    Application.CommandBars("Worksheet Menu Bar").Enabled = False
    With ActiveWindow
    .DisplayHeadings = False
    .DisplayHorizontalScrollBar = False
    .DisplayVerticalScrollBar = False
    .DisplayWorkbookTabs = False
    End With

    End Sub

    Private Sub Workbook_Deactivate()
    Application.CommandBars("Standard").Visible = True
    Application.CommandBars("Formatting").Visible = True
    Application.CommandBars("Worksheet Menu Bar").Enabled = True

    End Sub

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
  •