Best approach to manage a shared macro

[EchoPassenger]

I have multiple users who share several macros.

Currently, my approach has been to hold the macros in an add-in, and then store that file on a network drive.
Each user then has another add-in installed locally, which opens the shared add-in file on the network, immediately when Excel is opened.

This all works fine, however, anytime I need to update the macros I have to get all the users to close Excel as the shared add-in file is locked, whether it's in use or not.
So my question is, can I adjust this approach some way so I don't have to kick everyone out of Excel each time?
e.g. Could the shared add-in only be called by a macro button in the toolbar rather than when Excel is opened etc.

Thanks.

[spitfireblue]

Why not just have a copy of the add-in that you can work on and then you only have to kick everyone out when you copy that across to the shared folder?

[EchoPassenger]

Thanks - that's actually what I have been doing - but it's still a bit inconvenient to get everyone to close down Excel at the same time.

I need an approach that preferably needs no action from the users.

[Norie]

Couldn't you replace the add-in with the updated version overnight or at some other convenient time?

[EchoPassenger]

Not really, the team that shares the macro have early and late working hours. Plus the habit of people is to just leave applications open when they go home and just lock their PC rather than turn it off.

I suppose the question I really just need the answer to is... is there any way to load an add-in at the click of a button rather than when Excel is opened?

[spitfireblue]

You could create a second add in that has a macro that opens the .xlam file of your current add in (rather than opening it when excel opens) and setup a keyboard shortcut or button for users to open it.

[scottiex]

I suggest you make the add-in read only.

The way I have it set up is getting it to open a read only excel file that I make hidden.

That works without issue for me.

[EchoPassenger]

I suggest you make the add-in read only.

The way I have it set up is getting it to open a read only excel file that I make hidden.

That works without issue for me.

That's an interesting idea.

I guess rather than making the shared add-in read-only, I could simply set the local add-in to open the shared add-in as read-only.

I'm using the Workbooks.Open method, so I imagine I can just add ReadOnly:=True to the end and it should work?

[scottiex]

Yes that should work.
To do that you would have to update all their local add-ins, but maybe you have that setup so that it is easy.

[EchoPassenger]

Sorry to bump a previously solved thread, but I have a very odd issue with this one.

I went with the 'ReadOnly:=True' method, so my local add-in opens with Excel and immediately loads the shared add-in as read-only, however, I have experienced some very odd behavior whereby it works the first couple of times, but then inexplicably the shared add-in file get's deleted!? (Not the contents, the actual file itself). There's absolutely nothing in the code that could do this, so I guess possibly it's down to network/security software interpreting the add-in behavoir incorrectly and deleteing the file? Has anybody experienced this kind of thing?

Meanwhile, instead of programmatically opening the shared add-in as read-only, I've resorted to just ticking the read-only box in the file properties... but now I'm even having problems with this.
When I try to edit the file (if one of my users has Excel open) it tells me that the file is in use, even though the read-only flag is set. I can only assume that because it is technically being opened from within Excel that the read-only flag is ignored?

Can anybody confirm this, and/or suggest how it could be resolved?

[scottiex]

Meanwhile, instead of programmatically opening the shared add-in as read-only, I've resorted to just ticking the read-only box in the file properties...

This is how I do it.

When I try to edit the file (if one of my users has Excel open) it tells me that the file is in use, even though the read-only flag is set.

I don't not have this issue.
However - maybe someone still has the file open since before you made it read only? (maybe someone can reset that for you)
have you been able to edit the file since then?

[EchoPassenger]

I don't not have this issue.
However - maybe someone still has the file open since before you made it read only? (maybe someone can reset that for you)
have you been able to edit the file since then?

No it can't be that. When I couldn't edit it I actually saved a new version of the file, then once everyone was out of Excel I was able to delete the old file and rename the new one. I also made sure that it was flagged as read-only before doing so.

I just can't shake the idea that by having the shared add-in opened from within Excel, it is ignoring the read-only flag; after all, is it not the case that in order to edit a read-only file you have to open it from within Excel? Is there any resource or documentation available that could confirm this suspicion?

[scottiex]

No it can't be that. When I couldn't edit it I actually saved a new version of the file, then once everyone was out of Excel I was able to delete the old file and rename the new one. I also made sure that it was flagged as read-only before doing so.

I just can't shake the idea that by having the shared add-in opened from within Excel, it is ignoring the read-only flag; after all, is it not the case that in order to edit a read-only file you have to open it from within Excel? Is there any resource or documentation available that could confirm this suspicion?

Well even if it is you - it definitely isn't for me as I make changes all the time to mine (deleting and replacing) and never get told it is in use.

Are you sure it is the other user that is locking the file? If you run a test (getting them to lock it) and then they get out you can edit it right?