Closing all toolbars upon opening a workbook

[Todd_M]

Hi-
Im using the following code to hide all toolbars in the openworkbook event handler upon opening a workbook. It doesn't do anything.



Private Sub Workbook_Open()
Dim tb As CommandBar
Dim tbnum As Integer
Dim tbsheet As Worksheet
Set tbsheet = Sheets("tbsheet")
Application.ScreenUpdating = False
tbsheet.Cells.Clear
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.ScreenUpdating = True

End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim tbsheet As Worksheet
Set tbsheet = Sheets("tbsheet")
Application.ScreenUpdating = False
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


Unless I add a sheet called "tbsheet", I get a error saying "tbsheet" is out of range. When I add a sheet with the name, the workbook opens with the tool bars still showing.

[Mark O'Brien]

To answer the first question, to make the toolbars invisible, all you need to do is something like this:

  Dim tb As CommandBar

  

  For Each tb In CommandBars

    tb.Enabled = False

  Next

This is instead of using the "Visible" method. The reason it craps out unless you've got that worksheetis because your code is trying to write all of the names of the visible toolbars to that worksheet so that the application can be reset when you are closing. So if the code is trying to reference this sheet and it's not there, it doesn't work. I recommend keeping that sheet in the work book.