Hi all,
I've got a bit of a conundrum. I'm fairly rusty with VBA and I need to create a work distribution system that allows users to run a macro on their worksheet which takes a row from a centralised spreadsheet. Each row is a unique piece of work and so must only be taken once.
At this stage I will note that this is an interim process for work distribution and due to an abundance of restrictions placed down upon me, there is no option to use anything but excel. Not an ideal situation but hopefully you can help!
So far I have set up the central spreadsheet and password protected it.
I have created a front end worksheet that accesses the central sheet, grabs a unique piece of work, updates a column to ensure the same row doesn't get taken by subsequent runs and then saves.
This works fine.
The problem I encounter is when 2 network users simultaneously click to run the macro. One of them gets a spreadsheet busy error and a custom retry message, which I am comfortable with. The other user gets a password pop up followed by a 13 type mismatch error.
I repeat that this only occurrs when running simultaneously. When a single user runs it, the password is not requested (as I set the password early on in my code) and it moves the data without a glitch.
My desired result is that when both users click at the same time, one of the users successfully gets the piece of work as normal and the other gets the busy message.
I'm perplexed
Please help!
Bookmarks