Since the commandbar will not exist when the Deactivate event fires after
the Before_Close event you should just skip over the error:

Private Sub Workbook_Deactivate
On Error Resume Next
Application.CommandBars("MyCommandBar").Visible = False
End Sub


--
Jim
wrote in message
oups.com...
| Hi, I asked this question last week without any response. I thought
| I'd try again since we're implementing this system in several places
| now throughout our operations. Here goes...
|
| I have a several custom command bars, I want to show each always and
| only with a specific worksheet in a specific workbook. Each command
| bar is stored with its respective 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".
|
| I think it gives priority to the beforeclose/delete command, then
| can't hide the already deleted command bar, causing trouble for me.
|
| In the meantime, I've commented out the "before_close" code so the
| command bar is only ever hidden. But: as we get the system up and
| running, more and more workbooks have their own custom command bars to
| clutter things up. I'd rather not have the command bars from closed
| workbooks available at all. Is there some sort of code that can tell
| excel not to bother trying to hide the command bar if the deactivation
| is due to closing the workbook?
|