Hiding a toolbar

  • Thread starter Thread starter Shatin
  • Start date Start date
S

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.
 
Try prefacing commandbars with application

Application.CommandBars("Custom Toolbar").Visible
 
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.
 
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
 
Back
Top