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
- garbo7441Lv 71 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
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.
- FredLv 71 year ago
Make the macros "global" instead of applying only to a specific spreadsheet.