×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Toolbar Management
2

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

RE: Toolbar Management

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

RE: Toolbar Management

(OP)
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...

RE: Toolbar Management

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

RE: Toolbar Management

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.

RE: Toolbar Management

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

RE: Toolbar Management

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

RE: Toolbar Management

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.

RE: Toolbar Management

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?

RE: Toolbar Management

(OP)
The solution posted by yakpol does the trick, thanks all.  

By the way, we run Excel 97

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close