+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    02-21-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2010
    Posts
    96

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

    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?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

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

    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.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    07-26-2012
    Location
    USA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    351

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

    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

  4. #4
    Registered User
    Join Date
    02-21-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Office 2010
    Posts
    96

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

    Quote Originally Posted by Leith Ross View Post
    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.


    Quote Originally Posted by VBA FTW View Post
    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.

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

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

    Hello Spritz,

    You should post your macro code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1