macro workbook other users

[mikeover]

Hello. I have a workbook (not Personal.xls) that contains my macros, called My_Macros.xls and another workbook with data, let's say its called Data.xls. When I am logged on, I open both workbooks, and from Data.xls I run the macro stored in My_Macros.xls and it works fine. These files are stored on a shared drive.

However, when other users try to do the same thing, it doesn't work. VBA spits out some message about being unable to find the worksheet it is trying to activate.

I have read lots of posts about distributing your macro to other users but it seemed like they mostly applied to those people who have their macros stored in Personal.xls. But mine are in a separate workbook.

How can I make other users able to use my macros that I have stored in My_Macros.xls on a shared drive? It seems like there is some sort of permissions problem but I am not sure. Thanks.

 

[Domski]

Have you looked at distributing them as an add-in? This is often the recommended method for sharing macros.

Dom

 

[mikeover]

Can you distribute as an add-in even if they are not in your personal.xls file? Are these the correct steps?

1. Open My_Macros.xls and save as My_Macros.addin.
2. Have other user log into system.
3. Open Excel, Tools -> Addins -> Browse, find the addin
4. Save as their own Their_Macros.xls???
5. And then open Data.xls and run the macro from Their_Macros.xls???

Are those the correct steps?

Thanks.

 

[Domski]

Sure, it would be normal to pop them in a blank workbook to create your add-in from rather than the personal.xls workbook.

There are plenty of guides to distibuting add-ins. I used the steps in John Walkenbach's Excel Power Programming 2003 when I created a couple for work but have a look here or do Google for 'Distributing macros as an add-in' and you should get quite a few good pointers.

Dom

 

[mikeover]

Ok I got it working. THanks.