Command Bar Woes

[lisamariechemistry]

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?

 

[Jim Rech]

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
| 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?
|

 

[hira_tanwir]

That seems simple enough. Thanks!