Results 1 to 6 of 6

Hide all Toolbars on Workbook Open - error

This is a discussion on Hide all Toolbars on Workbook Open - error within the Excel Questions forums, part of the Question Forums category; I'm trying to use the following code in a Workbook_Open private sub to hide all Toolbars when my Workbook opens. ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Phoenix, Arizona
    Posts
    455

    Default

    I'm trying to use the following code in a Workbook_Open private sub to hide all Toolbars when my Workbook opens. When my Workbook opens I get a Runtime Error 424 Object Required error at the 'For Each TB In CommandBars' line. Can anyone tell me what may be wrong? Thanks!

    'Author John Walkenbach _
    modified by Tushar Mehta 2002-07-28 _
    Make TBSheet a module level variable; ensures the _
    restore process works when switching workbooks; _
    Also ensures the process doesn't fault in the absence _
    of a worksheet named TBSheet.
    Dim TBSheet As Worksheet
    Dim TB As CommandBar
    Dim TBNum As Integer
    On Error Resume Next
    Set TBSheet = Worksheets("TBSheet")
    On Error GoTo 0
    If TBSheet Is Nothing Then Exit Sub '<<<<
    Application.ScreenUpdating = False
    ' Clear the sheet
    TBSheet.Cells.Clear
    ' Hide all visible toolbars and store 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.CommandBars("Full Screen").Visible = False
    Application.ScreenUpdating = True
    End Sub

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default

    Replace CommandBars with Application.CommandBars

    That should take care of the error.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Phoenix, Arizona
    Posts
    455

    Default

    Thanks Juan! That took care of it.
    But now I'm getting an error when I try to turn the Toolbars back on in the Workbook_BeforeClose sub. It errors on 'TBSheet' in the following code:

    'Sub RestoreToolbars
    Dim cell As Range
    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

    Any ideas?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default

    Where is TBSheet dimmed ? at the top of the module or inside the Workbook_Open event ? should be at the top. Right after "Option Explicit" if you have it there...
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    On 2002-09-05 18:35, RogerC wrote:
    Thanks Juan! That took care of it.
    But now I'm getting an error when I try to turn the Toolbars back on in the Workbook_BeforeClose sub. It errors on 'TBSheet' in the following code:

    'Sub RestoreToolbars
    Dim cell As Range
    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

    Any ideas?
    You Need to declare TBSheet @ the Module level ie
    In the Thisworkbook declarations section have

    Dim TBSheet as Wroksheet.....take this out of the Workbook open event as well.
    Kind Regards,
    Ivan F Moala From the City of Sails

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Phoenix, Arizona
    Posts
    455

    Default

    Thanks Juan and Ivan - That took care of the error. But the Sub doesn't seem to work. The Sub used to hide all toolbars works fine.. the toolbar names are listed on the TBSheet. But when I close the Workbook, the toolbars are not restored. Any idea where to look from here?

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
  •  


DMCA.com