Hide all Toolbars on Workbook Open

[RogerC]

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

 

[Barry Katcher]

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)]

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>

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

 

[RogerC]

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.