Addin Macro in Toobar

[zSplash]

I have a non-private macro ("ShowIt") in my installed addin ("FLC.xla") that
I want to reference in "myToolbar". I have attached myToolbar to the addin,
as well as to the specfic workbook ("myWorkbook.xls")

When I assign the macro ("ShowIt") to myToolbar, w/o reference to the addin,
it automatically changes to reference "myWorkbook!ShowIt" (instead of just
("ShowIt"). The "ShowIt" macro is not in myWorkbook, but in myAddin! I
have tried assigning the macro to myToolbar by referencing "myAddin!ShowIt",
but that isn't recognized.

So: How do I reference an addin macro in a toolbar for a workbook to which
the addin is installed? Am I goofing up by attaching the toolbar to the
addin, or what's the deal?

TIA

 

[Leith Ross]

Hello zSplash,

All macros in an Excel Addin are Private to other Workbooks, even i
you declared the macro as Public in your Addin.

To work around this problem, you can create a worksheet to hold all th
macros and have that worksheet always run when you open Excel. To d
this, run Windows Explorer and go to C:\Program Files\Microsof
Office\Office\Xlstart (assuming you used the default location when yo
installed Office).

Right-click the right pane of Xlstart and choose New, Text Document
Name your new document personal.xls and press Enter. When asked if yo
want to change the extension, click Yes.

Now double-click your new file to open it in Excel. Next, choos
Window, Hide and then choose File, Exit. When asked if you want to sav
the Personal macro workbook, click Yes. The next time you run Excel
personal.xls will run but remain hidden.

To store your macros in personal.xls, choose Window, Unhide to unhid
personal.xls. After you finish storing your macros, choose Window, Hid
to hide personal.xls again

Sincerely,
Leith Ros

 

[Tom Ogilvy]

If you use code to create the toolbar each time the addin is loaded and
remove it when it is unloaded and put this code in your Addin, then I
believe you should be able to manage your toolbar without problems.

.. http://www.jkp-ads.com/articles/DistributeMacro03.htm

Might be useful.

It might be better to rethink your approach than try to kludge together a
solution. But, I have to admit it is not clear why you want it attached to
2 different "workbooks/addins"

 

[zSplash]

Thanks for the suggested approaches. I will try them out.

I can't believe it's that difficult in Excel to attach code to individual
(but not all) workbooks! What I want to do is perfectly doable in Word,
which usually isn't as "friendly" as Excel. Uff-dah!

I have code that I want to be available for many workbooks, but don't want
to just insert it in my Personal.xls. I thought an .xla file is
complementary to a .dot file in Word, is that not the case?

st.

 

[zSplash]

I thought we were trying to get away from storing code which is relevant to
only certain workbooks in our Personal.xls. I will try what you suggest,
Leith. Thanks for your input.

st.

 

[Tom Ogilvy]

I am sure it is easy and it certainly doesn't require the personal.xls. In
fact, I never suggested using the personal.xls - in fact, quite the
opposite. Your problem may be that you are trying to behave in Excel as you
would in Word and what you are doing may not be necessary.