Assign Macro

[AllenL]

Is it possible to assign one macro to a button in toolbar to run different workbooks??? So each workbook can run the macro independently and not to rely on the other workbooks.
I copy the macro's VBA code to each workbook. However, when I run the macro, it still open the workbook which the macro is last assigned by.
What should I do??? Please HELP

Thanks in advance

[smozgur]

Hi Allen,

There is a file called as Personal.xls. It is personal workbook and it opened everytime you open a workbook, and it is hidden as default. You can store your macros in it so you dont need to copy macros in all workbooks that you want to use in. To create a personal.xls if you have not already just Tools_Macro_RecordNewMacro and select Personal Macro Workbook for "store macro in" choice. After recording a simple macro by this way you can goto to VBE and see the Personal.xls file. Just move your macros there.

Thats it.

Regards

[AllenL]

Where is this Personal.XLS file saved?
After I copy the macro to the Personal.XLS, do I still need the VBA code that I copy in each workbook??
Thanks in advance

[smozgur]

It should be saved in C:WINDOWSApplication DataMicrosoftExcelXLSTART when you first create a macro in Personal Macro Book but you dont need to find that file because it would be opened automaticially. Just record a macro by selecting Store Macro In Personal Macro Book. Then goto VBE. You will see what i meant.

Then no, you dont need to copy all common macros anymore.

Regards

[AJ]

Hiya,

It really depends on your set-up, but it's likely to be something like:
C:program filesmicrosoft officeofficexlstart
or similar.
The beauty of the personal.xls is that it loads it everytime you start Excel (like a normal.dot in Word).

So you can just keep stuff in there and not worry about copying code.

Rgds
AJ

[AllenL]

If my company need to distribut all the workbooks to the other companies by e-mail. Do I need to copy that Personal.xls file to make the macro run?
Thanks in advance

[smozgur]

Oh no,

I mean yes it is possible but not right way i think, distributing your personal.xls, because their personal.xls files would be destroyed and/or you maynot want to distribute all your personal macros. You should better write a macro which transfers these macros into the document which would be shared when you want to send them. Or if it is not problem that all personal macro books are same then you can send also personal.xls

[AllenL]

If I only have one macro in Personal.xls. Is it a good idea to just send the Personal.xls???
When the user receive the Personal.xls, does it have to be put into the XLSTART folder to work, or doesn't matter where the user put it??
Thanks in advance

[daleyman]

this would be fustrating for the end users, to have to go and save a file to that specific file location, unless you want to distribute it so that everyone has the "always on the toolbar" button, in which case you would have to issue instructions or put a macro in the personal.xls to set this up for your users. if you're going to do this might as well put some code in the personal.xls to go save it to the write location, just make sure you cover your bases as different operating systems or versions of excel probably use different folders for this.

if it just for use in the workbook, then i would put the code in the workbook and put a button on the sheet or something like that. I have personally never needed to distribute a personal.xls, except maybe to help out the guy sitting beside me, for general distribution i would always build a self contained workbook, with menu page and buttons on that page.