workbook specific macro icons

[michael_amon]

I have written a graph analysis macro using VB (Excel 2000 Premium) and
assigned a customized toolbar / icon to the macro. It runs well, but
it's helpful to rename the file after an analysis is complete under a
more desriptive name.

When I do this however, even though the macro is still present in the
newly named file (under Tools-macro-macros...) the icon is looking for
the macro from the original file, and then opens the original file to
run the macro in the newly created file. So I end up with 2 different
instances of Excel running. Is there any way that an icon can be
assigned to macro and remain specific for that workbook, regardless of
the workbook's actual filename?

I hope that my description is clear enough!

Cheers,
Mike

 

[Robin Hammond]

Mike,

You need to look at
View, Toolbars, Customise, Attach, and attach the toolbar to the file in
question.

However, common experience is that this is not 100% reliable when migrating
workbooks from one machine to another.

When creating more serious systems, most of us will therefore embed the
toolbar pics in the file in question (usually an add-in) and write custom
code to create the toolbar based on these pics. There's some code on John
Walkenbach's site that can get you started on that route if you want to go
that way.

Robin Hammond
www.enhanceddatasystems.com

 

[Mike]

Robin,

Thanks, I have done the View, Toolbars, Customize, Attach process and
it does attach the custon icon I made, but when pressing the icon, it
goes back to look for the original .xls file in the location where the
macro was assigned/attached (ie desktop etc).

I don't think I can distribute the as an add-in as I need to give a
fair bit of user interaction and to display several graphs.

I am taking your advice about creating custom toolbars on the fly from
John Wlakenbach's site though... Unfortunately, I am pretty new at
this, as this is my first program and I'm doing alot of trial and
error! So I may be a little while, but I'll let you know how it
goes...

Cheers,
Mike

 

[Dave Peterson]

Just a couple of links:

If you want to add an option to the worksheet menu bar, I really like the way
John Walkenbach does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)