Macro Avaliable to Any Excel Workbook that is Opened

[jo15765]

I have a macro that I want to use in numerous excel workbooks. What I have been doing is just copying/pasting in a new module etc etc. My ? is, is there a way in excel 2000 to make a macro available to ANY workbook that is opened? In a sense I guess that would be embedding the macro to the "bones" of excel2000, or maybe it's better called a global macro?

 

[jo15765]

Recording the macro made the VBA Project.personal.xls folder appear. Now if I copy my procedure into the personal folder, Do I save it under the Sheet1 or this Workbook, so that it will move from workbook to workbook with me?

 

[HalfAce]

Can we see the code? - or at least the first few lines if it's real long?

What type of routine it is (user called sub, sheet change event sub, etc.) determines where you want to store it.

 

[jo15765]

Public Sub RefreshOnOpen()
Dim wks As Worksheet
Dim qt As QueryTable
 
For Each wks In Worksheets
For Each qt In wks.QueryTables
qt.Refresh BackgroundQuery:=False
Next qt
Next wks
 
Set qt = Nothing
Set wks = Nothing
End Sub

 

[HalfAce]

Yep, for that I would insert a standard module into your personal.xls and store your routine in there.

Then, when you want to call it from other workbooks, you'd just call it like this:

Application.Run "PERSONAL.XLS!RefreshOnOpen"

Does that help?

 

[jo15765]

So for every workbook I want to run this code in, I would have to go into the View Code of the workbook, and add this line of code:

Application.Run "PERSONAL.XLS!RefreshOnOpen"
</pre>

And then I would run it the same way as I would if I was in my "main" worksheet that has the procedure stored?

 

[HalfAce]

for every workbook I want to run this code in, I would have to go into the View Code of the workbook, and add this line of code:

How do you intend to call this routine?
By the user clicking a button or something? If so, then yes,simply make the button and assign that Application.Run line of code to your button in a standard code module in the new workbook.

Is this how you will execute the routine, or will you be using it in a Workbook_Open routine or some other kind of event?

 

[jo15765]

Ideally, I would want to run it the same way in other workbooks as I do in the "main" workbook. By going to Tools--->Macros--->Macros, and then choosing the macro and selecting Run. That way it runs when I want it to!

 

[HalfAce]

yes, then a standard module within the Personal.xls is the way to go.
To run it the way you've described, you'd hit Tools > Macro > Macros... and in the lower portion of the 'Macro' window you'll see a drop-down labeled 'Macros in:' - you want to open that up and choose "PERSONAL.XLS".

(Or, you could install a button (or pretty much any kind of object) onto the sheet and assign that macro to it so all you would have to do is click it to execute your code.)

 

[jo15765]

OMG That's awesome!!!! MUCH faster, than me inserting the module into EVERY workbook I open! Thank you for your help!

And I went against the button option, due to I email these reports to other people, and I don't want to give them any "noticeable" way to alter the data that I am sending them.

 

[HalfAce]

Cool. Glad it's all figured out.
And you're right about not installing a button if you're going to distribute the workbook. It won't let them alter your data (because what you have in your personal.xls workbook won't be in theirs') but it would just be a button that (at best) won't do anything and (at worst), generate an error for them because it can't find the routine it's calling for.