Easy way to use a macro in multiple (25+) workbooks

**Spritz** · 05-06-2013, 01:59 PM

Hi there.

I have a macro that i use in hundreds of workbooks. Is there a way i can use that macro in all the workbooks without having to right click the 40 or so buttons on every workbook? Right now if i have to edit anything in one of the workbooks i do it for the one and just use save-as for the rest of them. However, if i do that, the macro location will keep on jumping around. So later on when i need to use the macro on one workbook it will open the last workbook i saved the macro on.

The macro is a simple macro to change the inside color of an autoshape when clicked.

Is this possible?

**Leith Ross** · 05-06-2013, 02:19 PM

Hello Spritz,

You can add the macro to your Personal.xls workbook, add a control to the toolbar (XP and earlier), or create an Add-In.

**VBA FTW** · 05-06-2013, 02:34 PM

I'm not entirely clear what you're asking, but I think I'm doing something similar. If you're looking for an easy way to update all the workbooks, read on. If not, stop here.

I have 20+ workbooks that have round 18 sheets, all workbooks are the same except for 2 sheets in each workbook. I've created myself a little userform to make my edits on all of them. On my userform, I have a combobox that lists each worksheet, a textbox to type my cell range into (A12 for example), and another textbox for the formula or text I need to change. I just select the sheet I want to edit, type in the cell number, and paste the formula to my formula textbox. I then stick each workbook in 1 folder. When I hit the button, it loops through each workbook, unprotects the sheets, replaces the formula in the cell I specified on my userform, protects the sheets, saves, closes, and moves on to the next workbook until all are done. If you're interested I could attach the file, let me know

**Spritz** · 05-06-2013, 03:10 PM

Originally Posted by Leith Ross

Hello Spritz,

You can add the macro to your Personal.xls workbook, add a control to the toolbar (XP and earlier), or create an Add-In.

Hi Leith,

Thank you for your response. If i was to store my macros in the Personal workbook can i change the location of that workbook? The reason i am asking this is that all the workbooks i require the macro in are stored on a network drive that is accessed by different people. If one of these people opens a workbook when my computer is off and needs to use that macro, they will be unable to do so.

Originally Posted by VBA FTW

I'm not entirely clear what you're asking, but I think I'm doing something similar. If you're looking for an easy way to update all the workbooks, read on. If not, stop here.

I have 20+ workbooks that have round 18 sheets, all workbooks are the same except for 2 sheets in each workbook. I've created myself a little userform to make my edits on all of them. On my userform, I have a combobox that lists each worksheet, a textbox to type my cell range into (A12 for example), and another textbox for the formula or text I need to change. I just select the sheet I want to edit, type in the cell number, and paste the formula to my formula textbox. I then stick each workbook in 1 folder. When I hit the button, it loops through each workbook, unprotects the sheets, replaces the formula in the cell I specified on my userform, protects the sheets, saves, closes, and moves on to the next workbook until all are done. If you're interested I could attach the file, let me know

This sounds like it would be a great idea for changing formulas But i'm not sure how it would work for my purpose. I have a sheet with 36 autoshape circles. Each circle has a number in it. I have a macro that changes the inside color of the circle to green when you click on the circle and changes back when you click it again. I need to be able to use this macro in hundreds of workbooks. I don't want to have to open each workbook, ctrl + click on all 36 circles and right click -> assign macro for every workbook.