Assign Macro

AllenL

Board Regular
Joined
Mar 14, 2002
Messages
67
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top