I created multiple macros and vba programming for one excel file I want to use it in all workbooks of the same form?

Like put it as an add in option

2 Answers

  • 1 year ago

    You can do this using the 'Personal.xlsm' method for macros. VBA event handlers are specific to the workbook/worksheets they are created in.

    For macros, create a new workbook and copy all macros you wish to use into modules in that workbook. Create a New Folder in the root C:\ directory named 'Personal'. Save the workbook as 'Personal.xlsm' in the newly created folder.

    Go to File > Options.

    Select the Advanced options

    Scroll down to General options near the bottom of the Advanced options menu.

    In the 'At startup, open all files in:' text box, enter the path to your Personal folder, i.e. C\:Personal

    Now, every time you open any Excel workbook, the Personal.xlsm workbook will also automatically open, providing access to all macros in it for any other open workbook. Keyboard shortcuts you created in the Personal.xlsm workbook will function in the workbook you wish to manipulate.

    The minor irritation to this process is that the Personal workbook will open first, whether you open Excel or a specific workbook. You then have to minimize Personal and maximize the workbook you wish to manipulate.

    To a certain extent you can minimize this by adding this Workbook_Open event handler to your Personal.xlsm workbook.

    Copy this event handler to the clipboard:

    Private Sub Workbook_Open()

    ActiveWindow.WindowState = xlMinimized

    End Sub

    Access the VBE, double click 'This Workbook' in the MS Excel objects top left, and paste into the editing area to the right. Save the Personal workbook.

    If you now open Excel (not a specific workbook you wish to modify), the Personal workbook will be minimized and you can then do a File > Open to bring up the desired workbook.

    Double clicking a specific workbook will require minimize/maximize action on your part.

  • Fred
    Lv 7
    1 year ago

    Make the macros "global" instead of applying only to a specific spreadsheet.


Still have questions? Get your answers by asking now.