VBA Share/Unshare with Multiple Users Saving

[jrepko11]

Hello,

I've looked through many posts but I don't think I can find any for my problem (sorry if I'm wrong).

I have a shared workbook that many people will be using (and probably at the same time). I have a few macros in the book that, when run, unshare the book, unprotect the particular sheet, and then re-protect and re-share. The problem is that when the macro unshares, it automatically kicks out the other users ("This action will remove the workbook from shared use. The change history will be erased, and other users who are editing this workbook will not be able to save their changes, even if you share this workbook again").

Because the workbook is saved when it is shared, any other user who has it open can now not save their changes because they are disconnected from the workbook.

Is there any way around this? My first option would be some way to allow users to update the workbook at the same time. If there's no possible way to do this, then I will have to re-write my macros so that they work within a shared workbook.

Any help is GREATLY appreciated, thank you!!!


I am using Excel 2003

 

[jrepko11]

Bump. Any ideas?

 

[ScottR]

I think you're out of luck relying on an Excel solution, that's its designed behaviour. In those circumstances I imagine most of us would be using an actual database as a back end, with an Excel interface.

I'd personally always do that over any form of workbook sharing unless it was the most trivially simple thing.

 

[jrepko11]

I originally wanted to use a database solution. I know my crazy excel workbook is not the right tool for this---it was not really my choice to say the least. Unless anyone can think of any other way, I will try to revamp my macros in order to work in a shared workbook....

 

[jrepko11]

Could I maybe run something to save the workbook as a different name and then merge the workbook changes to the original through a macro when they save? I have no idea what the implications would be on speed or if it's even capable...but does anyone think it could work???

 

[jrepko11]

I have found out that there isn't really any way around this issue, however, I don't believe there is a huge risk of losing data because of it. This is because when the macro runs, it automatically saves the workbook. If any other users are in it, it will save their work as well.

Anyway, now I am trying to figure out another issue. If they do get the error message when they save (that they were cut off from the book) they get the option to save as a different workbook and then merge books. I don't want that to be an option because it could then create data integrity issues. I want to force them to just close the wb (without saving) and open the master.

Any way to automatically select "Cancel" if that message comes up? Or maybe a way to NOT allow the master to be merged with anything?

Thanks!

Using Excel 2003

 

[jrepko11]

I have found out that there isn't really any way around this issue, however, I don't believe there is a huge risk of losing data because of it. This is because when the macro runs, it automatically saves the workbook. If any other users are in it, it will save their work as well.

Anyway, now I am trying to figure out another issue. If they do get the error message when they save (that they were cut off from the book) they get the option to save as a different workbook and then merge books. I don't want that to be an option because it could then create data integrity issues. I want to force them to just close the wb (without saving) and open the master.

Any way to automatically select "Cancel" if that message comes up? Or maybe a way to NOT allow the master to be merged with anything?

Thanks!

Using Excel 2003