Toolbar Management
Toolbar Management
(OP)
I've created a custom toolbar that references VBA code, which is all well and good, but: I'd like the toolbar to be specific to the Excel template (".xlt" file) and any spreadsheet created using the template. This toolbar isn't useful to other spreadsheets, yet if I open a new spreadsheet, the toolbar appears. If I hit one of the buttons on the toolbar, Excel opens another file (the one that has the VBA code).
Is there a way around this??
Is there a way around this??
RE: Toolbar Management
If you save your macros in your "Personal Macro Workbook" instead of "This Workbook", your macros will be available whenever you open excel. If you're confused about where I'm getting these files, record a new macro. Before excel lets you record, it asks you to define the macro's name and where to store it (click on the drpo down box to find " Personal Macro Workbook"). Once you store a macro in your "personal workbook", it will be visable in your VBA editor. Now just transfer all your macros for your toolbar into a macro in the "personal workbook". This should solve your problem!
jproj
RE: Toolbar Management
What I'm after is NOT having the toolbar available to spreadsheets other than the one it was created for, which is to say I'd like it not to appear for a "new" workbook...
RE: Toolbar Management
RE: Toolbar Management
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.
RE: Toolbar Management
Sorry if I misunderstood your question, but a simple way to keep excel from opening another excel file (the one that has the VBA code) is to put all of your VBA code into your personal.xls file. When you do this, the files open (hidden of course) every time you open excel and if you click on your toolbar button, the code is accessible w/out excel having to open another file.
Another thing you might want to try is adding code that checks to see the type of file that is in use (a validation of sorts). If the new file is not the kind you want the toolbar on, then hide it, else leave it visible.
Hope this helps.
jproj
RE: Toolbar Management
I assume, your toolbar is attached to the file. To hide it after the workbook is closed add the following code to your workbook object in VBA:
Private Sub Workbook_Open()
Application.CommandBars("MyMenu").Visible = True
End Sub
Private Sub Workbook_BeforeClose()
Application.CommandBars("MyMenu").Visible = False
Application.CommandBars("MyMenu").Delete
End Sub
RE: Toolbar Management
Yakpol has presented the exact solution to your problem - I'll just supplement with a bit of background information:
1. Before you put in the Auto_Open/Close procedures in place, you open the workbook relevant to the command bar and do View>Toolbars>Customize...in the dialog box select the Toolbars tab and click Attach...
In the dialog now displayed attach your toolbar to the workbook - then save the workbook.
When you do this, the toolbar is saved WITH the workbook.
2. Whenever you make a new toolbar, Excel automatically adds it to the toolbars collection and the toolbar may show its face even when the related workbook is not open (which is what is happening in your case).
3. Deleting the toolbar in the Auto_Close procedure removes it from the Toolbars collection so you don't see it when the 'toolbar' workbook is not open. Whenever you open your 'toolbar' workbook, the toolbar is again available since it was saved with the workbook.
RE: Toolbar Management
An interesting dilema is that the
Application.CommandBars("MyMenu").Delete
code behaves differently in 97 and 2000. I forget which is which, but one deletes the commandbar from the application collection only, and one deletes it from the file as well. A test of the version of Excel is required, with different code for each, ie, an 'exit without saving' for the later after the commandbar has been deleted.
Back to Ecutrights original problem...
When he has multiple child workbooks (of the same *.xlt file) open simultaneously, how to swap 'ownership' (for want of a better word) of the commandbar in a tidy manner to the active workbook?
RE: Toolbar Management
By the way, we run Excel 97