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