Can't Disable Excel Custom ToolBar On Close

[TheAceMan1]

Howdy All . . .

Platform: Vista Home Premium, Excel 2003 SP3

Have a custom toolbar that I'd like to disable on close (so it doesn't appear globally). Using the [blue]Workbook_BeforeClose[/blue] or [blue]Workbook_Deactivate[/blue] events produces the following error:

[blue]Run-Time error '91':

Object variable or With block not set[/blue]

... and the code that produces the error:

[blue]Private Sub Workbook_BeforeClose(Cancel As Boolean)
   CommandBars("STS1").Enabled = False
End Sub

[green]'or[/green]

Private Sub Workbook_Deactivate(Cancel As Boolean)
   CommandBars("STS1").Enabled = False
End Sub[/blue]

If I set an object 1st, as in:

[blue]   Dim CB As CommandBar
   
   [COLOR=red yellow]Set CB = CommandBars("STS1")[/color]
   
   CB.Enabled = False
   
   Set CB = Nothing[/blue]

... the same error occurs on the highlighted line. I believe the [blue]Office11[/blue] reference is no longer available at this point.

Any Ideas on disabling the custom toolbar on close?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

To All . . .

Problem solved!

I had to wrap the code in a function:

[blue]Public Function DisableCustomCBar()
   CommandBars("STS1").Enabled = False
End Function[/blue]

Then from the workbooks [blue]BeforeClose[/blue] event, its:

[blue]Private Sub Workbook_BeforeClose(Cancel As Boolean)
   [purple][b]Run[/b][/purple] DisableCustomCBar
End Sub[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]