Command Bar show/hide/delete

[lisamariechemistry]

Quick question:

I have a custom command bar that I want to show always and only with a
specific worksheet in a specific workbook.

In "This Workbook" macro area I have

Private Sub Workbook_Activate
Application.CommandBars("MyCommandBar").Visible = True
End Sub

Private Sub Workbook_Deactivate
Application.CommandBars("MyCommandBar").Visible = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("MyCommandBar").Delete
End Sub

(I also have visible true/false to activate and deactivate the sheet,
but that is not where I seem to be having trouble)

Upon closing the workbook I get an error message: "Invalid Procedure
Call or Argument" that goes to the workbook_deactivate code when I hit
"debug".

But if I delete that part out then I can still see the command bar
upon opening (and activating) another workbook, although the error
message is gone. Help?

 

[Johz]

first put this into the beginning of each of those subs:

on error resume next

also into the deactivate-event then come back here if it still isn't working right... you're probably getting the error because you've already deleted your commandbar and you're trying to set its visible to false. the on error resume next -statement causes the code to run without stopping on the error.