Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Bar Woes
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Bar Woes
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? | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Bar Woes
That seems simple enough. Thanks!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation woes.... | Excel Programming | |||
More RGB Woes | Excel Programming | |||
Hyperlink Woes - Please Help | Excel Worksheet Functions | |||
For Each Next woes | Excel Programming | |||
ActiveSheet woes | Excel Programming |