PDA

View Full Version : Shared workbook with specific sheets being read only if in use?



Pancakes1032
12-19-2014, 01:39 PM
I have a workbook that I need to be in shared mode because of the amount of users needing access to it constantly. However I would like to make it so that if someone is already on a specific sheet, they will not be allowed to make any changes until that other user is off. Thank you!

SamT
12-19-2014, 03:00 PM
See: This Post (http://www.vbaexpress.com/forum/showthread.php?51020-VBA-code-to-transfer-data-to-different-workbooks&p=316740&viewfull=1#post316740) and Post # 12.

Are you sure that you are the same Pancakes I was working with 3 months ago on this same project?

Pancakes1032
12-19-2014, 04:02 PM
See: This Post (http://www.vbaexpress.com/forum/showthread.php?51020-VBA-code-to-transfer-data-to-different-workbooks&p=316740&viewfull=1#post316740) and Post # 12.

Are you sure that you are the same Pancakes I was working with 3 months ago on this same project?

Yes! I feel like my question was quite similar to that post. I just created a new workbook for another that will be strictly excel since not much data is needed for this department.

In your post #12 "The way VBA does things, it will use a Read Only copy of Master Log to enter all data, then check if Master Log is Locked, if not then open master Log, update it from the copy and close it. that entire process will only take a few seconds. The supervisors will rarely experience any delays let alone actually notice them."

What coding would I use get the Master Log in a read only format and then open it when unlocked and update all the data from the copy? That seems much better then having to have this file in a shared mode. My only concern is, if they open the read only Master log and enter an update in lets say "A1" because it was blank and they were able to enter the data there, but another employee on the same sheet at the same time enters different data in "A1" because they see that it was blank on their side; How would excel know to paste that data under whichever one was first? I know the chances of that happening are small, but with this department at certain times of the month, the possibility is actually very high.

SamT
12-19-2014, 06:04 PM
My idea at the time was for there to be one masterLog and several Supervisor's Logs and that the masterLog was not "used" by anybody, but was only opened Read/Write for updating in the morning when the new Assignments came in from the head office and once in the evening to record the day's work from the various Employee Assignment books. I estimate that this would take <= 5 minutes for 30 employees.

Check out the FileSystemObject Object


My only concern is, if they open the read only Master log and enter an update in lets say "A1" because it was blank and they were able to enter the data there, but another employee on the same sheet at the same time enters different data in "A1" because they see that it was blank on their side;

I am still on the old way of you doing things, so I don't see how that would be possible since each employee has their own sheet in the master log, and employee's aren't supposed to have access to the Master Log workbook.

If by "employee" you meant Supervisor, I thought that each super worked with different employee groups.

Pancakes1032
12-21-2014, 03:58 PM
Each employee does have their own sheet, the problem is that office assistants are the ones doing the assigning. With this specific department, there are 5 employees that need to be able to enter data on 1 sheet that would be in the master log. They can use their own workbook to do it, but they would be the ones actually entering the which is why there may be a conflict. For example, their job is customer service, so 1 workbook with 4 sheets. 1 sheet is customer call backs. So 5 different employees will need to be able to enter that customer's information on the master log in sheet 1 (customer call backs). The supervisor is the ones that get the alert when a customer's info is entered and therefore are the one that calls the customers, but the employees enter the info. I understand the chances of them (employees) needing to access it at the exact same time may be small but at certain times of the months the chances are very high. Hopefully that made sense.

SamT
12-21-2014, 05:35 PM
Stop "transitioning" and switch to Access.