Hide all Toolbars on Workbook Open - error

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
533
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
533
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?
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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...
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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.
 

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
533
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?
 

Forum statistics

Threads
1,143,920
Messages
5,721,541
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top