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