Hiding a toolbar
 

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.

Hiding a toolbar
 

Try prefacing commandbars with application

Application.CommandBars("Custom Toolbar").Visible

--
Regards,
Tom Ogilvy


"Shatin" wrote in message
...
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.

Hiding a toolbar
 

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.

"Tom Ogilvy" wrote in message
...
Try prefacing commandbars with application

Application.CommandBars("Custom Toolbar").Visible

--
Regards,
Tom Ogilvy


"Shatin" wrote in message
...
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.

Hiding a toolbar
 

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


"Shatin" wrote in message
...
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.

"Tom Ogilvy" wrote in message
...
Try prefacing commandbars with application

Application.CommandBars("Custom Toolbar").Visible

--
Regards,
Tom Ogilvy


"Shatin" wrote in message
...
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.