Hide all Toolbars on Workbook Open

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
533
I'm trying to use John Walkenbach's code to hide all toolbars when a Workbook opens and then restore them when the user closes the Workbook. It works on opening, but it doesn't restore the toolbars on closing. (The toolbar names are captured on the TBSheet). Can anyone give me an idea of what may be wrong?

Hide the Toolbars ---------

Private Sub Workbook_Open()
'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 Application.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
End Sub

Restore the Toolbars------

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim cell As Range
If TBSheet Is Nothing Then Exit Sub '<<<<
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Am I missing something? Without looking at the macros, but wondering out loud, how do you know the toolbars aren't restored on closing if, on opening, they are being hidden? Isn't this akin to standing in the kitchen and wondering if the fridge light really does go out when you close the door?
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Roger,

Give this a try (changes marked with an asterisk):<pre>
Private Sub Workbook_Open()
'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
Dim TBSheet As Worksheet '*

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 Application.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

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim cell As Range
Dim TBSheet As Worksheet '*

On Error Resume Next '*
Set TBSheet = Worksheets("TBSheet") '*
On Error GoTo 0 '*
If TBSheet Is Nothing Then Exit Sub '<<<< ????

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
On Error GoTo 0

Application.ScreenUpdating = True

End Sub</pre>

HTH

PS This isn't really relevant to your post - I'm just testing whether Mark's suggestion re the "Pre" tags works so I'm writing quite a long sentence!

EDIT : Yep, it works! :)

_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

caffeine_sample.gif
</gif>
This message was edited by Richie(UK) on 2002-10-03 16:43
 

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
533
Richie - That works... thanks for your help!

Barry - The problem was the toolbars were still gone when I opened a different workbook. It's not that I cared if the light was going out, I wanted to make sure the light came back on the next time I opened the fridge. :)
 

Forum statistics

Threads
1,144,148
Messages
5,722,792
Members
422,458
Latest member
Muirzy

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