How to use macro across all the systems ?

[kaushik03]

Hi All,


Say I have the below macro:


Sub MyMacro ()

MsgBox “Hi All”

End Sub


I want to access this macro from any computer in work station (say I have 50 computers, one for each user) whenever I open an excel application.


How to do this?


Regards,

Kaushik

 

[Debraj]

Hi Koushik..


Try to create ADD-INS and inform everyone in the office to install the same in their own excel..


https://dl.dropbox.com/u/78831150/Excel/macro%20across%20all%20the%20systems%20%28kaushik%29.xlam


Go to Excel-Option > Add-Ins> Manage Excel Add-Ins > GO > Browse the uploaded file and now this module is available in your own system..


Hope it helps..


Regards,

Deb

 

[kaushik03]

Hi Deb,


Thank you for your quick reply (as always...)


I am aware of this add-in creation technique that you have suggested. But I was wondering if it is possible to write some code under "Workbook_Open" event to install the code as add-in whenever an excel application is opened from any system and delete the code as well when the workbook is closed (write some code under "Workbook_BeforeClose" event). So that, we can avoid installing the same in multiple systems..


My apologies if I am trying to acheive something stupid which may be not at all possible...


Anyways, kemon acho?


PS: Clarification to the forum: How are you?


Regards,

Kaushik

 

[Wookiee]

I have an alternate solution to this issue. We use this in my office so that I can make macros available to all users relatively simply. We have a folder on our shared drive whose sole purpose is to house macros. The only file in the folder is (imaginatively enough), Macros.xlsb.


Then, we had everyone who uses Excel go into their Excel Options dialogue. (Please note: these instructions are given from an Excel 2007 setup.)


Excel Options | Advanced | At startup, open all files in


We then had them type in the full address to the Macros folder.


...MacrosMacros.xlsb


To make sure they don't have to enable macros every time they boot up Excel, we have them add that folder to the Trust Center:


Excel Options | Trust Center | Trust Center Settings | Trusted Locations

Add New Location | Browse to the folder | Click OK


By using this method, I was able to create several macros and even construct a macros toolbar to appear in the Add-Ins section so that they have one-button access to the most commonly-used macros.

 

[kaushik03]

Hi Wookiee,


Thank you for your valuable suggestions.


I have two quick questions here:


1)When you say the sole purpose of the folder(containing the xlsb file in the shared drive) is to house macros, do you mean to say all macros or macros which are frequently being used for some common purposes?


2)In this process, do all the users need to write the file path in "At startup, open all files in" dialogue box?


However, I will try your method once I will be in the office today and get back to you in case of any challenge.


Regards,

Kaushik

 

[Wookiee]

Hello, Kaushik. I hope my suggestion works as a viable solution for you. Please allow me to clarify my statements.


1 - The Macros folder doesn't hold all macros I and some others use, but it does hold all the macros which we want to make commonly available.


2 - Yes. Every user has to set up their Excel session with the folder in the startup path.


Best of luck!


Wookiee