Dear Excel Wizes!
I have a problem that I think I might have a solution for but I wanted to check with you guys first.
Multiple users are accesing the same spread sheet and are not satisfied with the limitations of the Excel share document function. (Links can not be created etc.)
I save all the data in Excel workbook "A". Then I start a new workbook "B" and make a user form with a nice GUI. Workbook "B" will then access data from workbook "A" (I do this because I need to have Workbook "B" as fast and possible and "clean" as possible).
My plan is thus to store local versions of Workbook "B" on different computers and have them contact Workbook "A" to store and load data. Kind of like a database. I plan to simply put a flag when a row is loaded to prevent it from being tampered with by another user. For example:
Adam is loading data from row number 12.
- I put a flag in the code saying that row number 12 is being accessed.
A couple of minutes later Christen is trying to load data from row number 12 and than gets a message that she cannot store any data on row number 12 at the moment.
Now, I guess that there must exist functions like this in VBA, maybe using semaphores or something, in that case, does anybody know how to use them. Or do you recon that my way will work satisfactory?
I mean, I guess it could work but I don't know what will happen when to users try to access the row at the same time, before the flag indicates that it is being accessed?
I also see a problem if a user wants to delete a row with a lower number. For example:
Adam is accessing row number 12.
Christen wants to delete the entire row number 10.
When Adam is done with his work and wants to save the data again, row no.12 is now row number 11 since Christen deleted row number 10. How can I overcome that problem?
Puh... A lot of info here but I would be really greatful for any answers! Do I have to involve a database, like SQL or Access or can I solve this with pure Excel VBA?