how to limit custom toolbar's display

[Dingding]

Hi, all

I am working on an application in Excel and I add a new toolbar to
excel by coding VBA. Here's the way that I wrote like:

Application.CommandBars.Add Name:= "My Toolbar", Temporary:=True

But if I open other excel workbooks at the same time, the toolbar also
appears in the window of those workbooks. It's really annoying and
leads to mistakes. Is there any way to prevent the toolbar displaying
in unrelated workbooks?

Thanks for any suggestions.

Dingding

 

[paul.robinson]

Hi, all

I am working on an application in Excel and I add a new toolbar to
excel by coding VBA. Here's the way that I wrote like:

Application.CommandBars.Add Name:= "My Toolbar", Temporary:=True

But if I open other excel workbooks at the same time, the toolbar also
appears in the window of those workbooks. It's really annoying and
leads to mistakes. Is there any way to prevent the toolbar displaying
in unrelated workbooks?

Thanks for any suggestions.

Dingding

Hi
If your code that inserts the menu is called CreateMenu() and your
code that removes the menu is called DestroyMenu() then create these
two subs in the ThisWorkBook code module;

Private Sub Workbook_Activate()
Call CreateMenu
End Sub
Private Sub Workbook_Deactivate()
Call DestroyMenu
End Sub

The Create and Destroy subs would be what you would have in your Open
and BeforeClose macros to add and remove the menu when you open/close
Excel.

regards
Paul

 

[Dingding]

Hi
If your code that inserts the menu is called CreateMenu() and your
code that removes the menu is called DestroyMenu() then create these
two subs in the ThisWorkBook code module;

Private Sub Workbook_Activate()
Call CreateMenu
End Sub
Private Sub Workbook_Deactivate()
Call DestroyMenu
End Sub

The Create and Destroy subs would be what you would have in your Open
and BeforeClose macros to add and remove the menu when you open/close
Excel.

regards
Paul

Hi,

Your suggestion works! Thanks a lot.

Dingding