Using macros in an external worksheet

[Guest]

What is the best way to setup Excel if I need to access macros in an external
worksheet and run them in a variety of worksheets? I tried saving them in a
..xls worksheet and then setup Excel to open the worksheet when the app opens,
but the worksheet is always displayed. Should I save the worksheet as a *.xla
and then add it as an add-in?

 

[dq]

Set the property ThisWorkbook.IsAddin to true and the book will be
hidden.

DQ

 

[Guest]

And just where would I place the statement? Which event procedure?

 

[dq]

You can do this either manually or put it in the Workbook_Open event.
(in the ThisWorkbook codepane)

DQ

 

[Guest]

So how is this different from saving the workbook with the macro as a .xla
file and then using Tools > AddIns to add it to the workbooks in which it
needs to run?

 

[dq]

Simple: not so much work

 

[Guest]

Would you please reread my last post. Your most recent response did not
answer the question.

 

[Dave Peterson]

I would save the file as an addin so the workbook will be hidden when it opens.

But then I'd have to give the users (myself???) some way of running those
procedures.

For additions 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)

=======
If you're going to save this addin with others and you have UDF's called from
cells in a worksheet, then I'd save the addin (DCH3Utils.xla) in a common
location -- a shared folder and tell the users to install the addin using the
UNC path name (not a mapped letter).

If that's not possible, then I'd tell the users to save it to the same folder
name:

C:\MyAddins\dch3utils.xla

Then tools|addins|browse and install it from there.

 

[Guest]

Thx - that's what I've been looking for.

I would save the file as an addin so the workbook will be hidden when it opens.

But then I'd have to give the users (myself???) some way of running those
procedures.

For additions 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)

=======
If you're going to save this addin with others and you have UDF's called from
cells in a worksheet, then I'd save the addin (DCH3Utils.xla) in a common
location -- a shared folder and tell the users to install the addin using the
UNC path name (not a mapped letter).

If that's not possible, then I'd tell the users to save it to the same folder
name:

C:\MyAddins\dch3utils.xla

Then tools|addins|browse and install it from there.