Best way to centralise some VBA?

[Johnny C]

A dept I have written some code for is bombarded each month with files (several thousand) which should be in a particular format, should have certain rangenames in the right places and have data validation and some dynamic lists.

Since the s/s come from a 3rd party Indian call centre, they don't stick to the format. I don't know why, they jsut don't. So I've written a little bit of VBA that puts the ranges names in the right places, adds dynamic lists, and adds data validation to some template rows.

So far so good. I attached a smiley face button to the code on the QAT and the people think it's great. But now they want to roll out 'The smiley face macro button' to a department of upto a hundred.

Now I could go and fix it into everyone's personal.xlsb and give some instructions to them to add the smiley face button to the QAT and connect it to the macro.

But, the indian call centre being what it is, the macro will need maintaining as over time extra functions will need to be built in as the call-centre staff find new ways to wreck the file templates. Which would mean updating a hundred personal.xlsb files 3x a month.


So, what is the best way to do this?

We don't want the security message to require macros to be enabled each time they press the button.

Nor do we want to get messages about 'Standard user has the file open - open as read-only'? each time we press the button.

I looked at a macro template file .xlmt but that opens up the remplate file every time they press the button.

I tried an add-in, that doesn't open the xlmt file each time the button is pressed.

But by default all my security notices are suppressed so I am waiting for some else to test if you need to enable macros each time it runs.


Someone however has mentioned that they read it is possible to create a centralised macro file that multiple people can have open at once with read-only access with macros in that Excel automatically trusts.

is this true? Can you deploy VBA like that? it would make sense and maybe XL2010 or VSTO can do it (we're using 2007).

Cheers

 

[Johnny C]

bump

 

[RoryA]

A centralised add-in would be my choice - see here for info. I'd customise it to add its own group to the Ribbon if all your users have 2007+

 

[Johnny C]

Cheers rorya (or should I call you Mary?)

I will have a play with adding a ribbon for it. Then they can have a big smiley face. They will like that.

 

[Johnny C]

Done plus they did indeed get a 'large' msoHappyFace. You can't imagine how delighted they are.

I made the xlam Read Only but I suspect that's unnecessary these days as it will take a local copy of the Add-In allowing a developer to access the 'mother' add-in.