Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Closing all toolbars upon opening a workbook

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

    Default

    Hi-
    Im using the following code to hide all toolbars in the openworkbook event handler upon opening a workbook. It doesn't do anything.



    Private Sub Workbook_Open()
    Dim tb As CommandBar
    Dim tbnum As Integer
    Dim tbsheet As Worksheet
    Set tbsheet = Sheets("tbsheet")
    Application.ScreenUpdating = False
    tbsheet.Cells.Clear
    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



    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim tbsheet As Worksheet
    Set tbsheet = Sheets("tbsheet")
    Application.ScreenUpdating = False
    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


    Unless I add a sheet called "tbsheet", I get a error saying "tbsheet" is out of range. When I add a sheet with the name, the workbook opens with the tool bars still showing.

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To answer the first question, to make the toolbars invisible, all you need to do is something like this:


    Dim tb As CommandBar

    For Each tb In CommandBars
    tb.Enabled = False
    Next



    This is instead of using the "Visible" method. The reason it craps out unless you've got that worksheetis because your code is trying to write all of the names of the visible toolbars to that worksheet so that the application can be reset when you are closing. So if the code is trying to reference this sheet and it's not there, it doesn't work. I recommend keeping that sheet in the work book.


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
  •