Hiding a toolbar

[Shatin]

I have a floating toolbar in a workbook. On certain worksheets where it is
not applicable, I want to hide it. I can do it without any problem with
following code:

Private Sub Worksheet_Activate()

If CommandBars("Custom Toolbar").Visible = True Then
CommandBars("Custom Toolbar").Visible = False
End If

End Sub

Since the toolbar is specific to the workbook, I also want to hide it when
another workbook is activated. So I try same code as above:

Private Sub Workbook_Deactivate()

If CommandBars("Custom Toolbar").Visible = True Then
CommandBars("Custom Toolbar").Visible = False
End If

End Sub

However this time I have a problem. The error message is
"CommandBars("Custom Toolbar")=...Object variable or with block variable not
set". I am not sure what the problem is since same code works in the the
case of worksheets.

 

[Tom Ogilvy]

Try prefacing commandbars with application

Application.CommandBars("Custom Toolbar").Visible

 

[Shatin]

Thanks, Tom. The magic word "application" does solve my problem although I
am not sure why.

Then an unexpected problem. When I close the workbook, there's a
workbook_close event to kill the toolbar. Since the toolbar has been killed,
the workbook_deactivate event cannot hide it, giving rise to an error. After
some thought, the problem is solved by having "On error goto Error_handler"
in the workbook_deactivate code. The Error_handler simply tells Excel to
exit sub. I tried to have simply "On error exit sub" but VBA didn't like it.

 

[Robert Rosenberg]

You can test for the existence of the commandbar but I'd go with your easier
Error Handler method.
--
_______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
http://www.r-cor.com