Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations MintJulep on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Toolbar Management 2

Status
Not open for further replies.
SoledWorker

SoledWorker

Mechanical
Joined
Aug 20, 2001
Messages
50
Location
US
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??
 
Sort by date Sort by votes
ecutright:

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
 
Upvote 0 Downvote
The macros aren't an issue, they work as desired:

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

 
Upvote 0 Downvote
jproj is correct. the toolbar must be associated with the intended workbook and NOT your personal workbook. Try creating a new workbook and copy your macro's toolbars into there and save them to "this workbook" and not "personal workbook".
 
Upvote 0 Downvote
The toolbars should also be created in the workbook they should reside. You have obviously linked them somehow to your personal.xls file. DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.
 
Upvote 0 Downvote
ecutright:

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
 
Upvote 0 Downvote
ecutright,
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

 
Upvote 0 Downvote
ecutright,
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.

 
Upvote 0 Downvote
I have used variations of yakpols and Malas solution in Excel95, 97 and 2000

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?
 
Upvote 0 Downvote
The solution posted by yakpol does the trick, thanks all.

By the way, we run Excel 97
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

REDesigner09
  • Locked
  • Question Question
Looking for Project Management Excel Template
Replies
5
Views
325
dcasto
dcasto
MigrantFirmWorker
  • Locked
  • Question Question
Excel custom toolbar file location
Replies
1
Views
156
IRstuff
IRstuff
Speedy
  • Locked
  • Question Question
Lost my Toolbars on Microsoft Word
Replies
3
Views
186
IRstuff
IRstuff

Part and Inventory Search

Sponsor

Back
Top