Sharing Excel macros

[tallbarb]

Someone asked me: "How can I save a macro on a network drive, so that all my cube mates can use it on all excel spreadsheets?"

What would be the best approach if:
1. Excel is NOT a networked application
2. Excel IS a networked application

 

[johnnygeo]

I would recommend putting all such macros in an add-in. Create a new workbook and cut & paste your macros into this new workbook. Save the workbook as an add-in (.xla file) on the network drive. A user will start Excel, go to Tools/Add-ins, and browse for the .xla from the network drive. Then he will activate (put a checkmark next to) the add-in, and he will be able to use any of the macros from the add-in.

The only drawback I can think of is if the user is not connected to the network (e.g. a laptop user), he may have some minor unpleasantness when he starts Excel.

 

[tallbarb]

Once the macros are pasted into the new .xla file and that file is attached as an Add-In, how are the macros run? I'd love to have a toolbar button to do this.

 

[tallbarb]

I found out how to add custom buttons for VB:

To add the VB code to a button on a toolbar, select Tools, Customize - click on the Commands tab. Select Macros from the Categories window. Select Custom Button from the Commands window and drag it into your toolbar. Click on the Modify Selection button in the dialog box. Type a name in the Name: window. Click on Text Only (Always) or Image and Text. Click on Assign Macro.... Type the name of the VB routine you want to call (must match the code exactly). Click Close. That's it. Or, you can go into the VBA editor and click into the routine you want to run and press <F5>.

Consider this issue resolved - thanks!~