global macro instead of specific workbook

[mp]

If I create a macro and toolbar to call it
while i'm in spreadsheet "a",
when I close spreadsheet "a", the toolbar still shows in excel
but if I click on it it opens spreadsheet "a" (in order to run the macro)
How to make toolbars be "generic"... not tied to a specific spreadsheet?
I exported the module to my code folders but don't see how to de couple the
code from the original spreadsheet.
thanks
mark

 

[Gord Dibben]

You can place all your macros in Personal Macro Workbook which makes them
available for all open workbooks.

Assign the macros on your Toolbar to Personal.xls

Or place the macros in a workbook and save that workbook as an Add-in which you
keep loaded through Tools>Add-ins

Also, it would be best to use code to create the Toolbar "on the fly" with code
when the Add-in loads.

See Debra Dalgleish's site for code to build a Toolbar, courtesy Dave Peterson.

http://www.contextures.on.ca/xlToolbar02.html


Gord Dibben MS Excel MVP

 

[mp]

Thanks Gord,
I went the addin route with the dynamic toolbar creation technique.
one of my routines that i'm calling created a userform using vbmodeless
so the form used to stay open
now going through the addin toolbar the form disappears after using it the
first time
is there a difference between how code works when loaded from an addin than
from a local module?
thanks
mark

 

[Gord Dibben]

I wouldn't think there is any difference.

Can't see your code so not much to go on.

Have you read the VBA help on "showmodal" property?

Your userform properties should be set to false.


Gord