Record (Row) Locking in Excel

[adambc]

And before anyone suggests we should be using an Asset Inventory tool, I am "working" as a volunteer for a local charitable organisation that has limited budget and is not currently in a position to invest in such a tool!

I have a Workbook that is an Asset Inventory (1000+ rows, 30 columns) which is driven by two UserForms:

i) ADD NEW records (assets); and
ii) UPDATE EXISTING records.

The Workbook is stored in a SharePoint folder and can be accessed by more than one (typically no more than four) User concurrently.

Problem 1 (ADD NEW) …

I am using an INVENTORY ID column as a unique key for each record eg INVnnnn, which is allocated when an ADD NEW UserForm is Saved by taking the value in the INVENTORY ID column of the last row in the main table and incrementing it by 1.

The problem is that because each User is effectively working on a standalone version when they open the Workbook, when a new INVENTORY ID is allocated, conflicts can arise.

Problem 2 (UPDATE EXISTING) …

The UPDATE EXISTING UserForm reads the current data into the form from the main table, which Users can update and Save.

Again, the problem is that because each User is effectively working on a standalone version when they open the Workbook, conflicts can arise (and merging changes doesn't really work in this instance!).

I fear the answer is “No” (I would be delighted if there is a way!), but is there a way round these problems?

For the UPDATE EXISTING problem I have tried using a helper column that toggles between “Unlocked” and “Locked” ie when a User invokes the UPDATE EXISTING form the value is changed to “Locked” and when they exit the form the value is changed back to “Unlocked”, which I check before loading the form, but this doesn’t work across effectively standalone versions.

Hope that makes sense; all answers appreciated, even if it’s to confirm it can’t be done!

Many thanks …

 

[offthelip]

I think the way I would get around your problem would be to have a "Master" workbook that is password protected and can only be opened by you and any vba that you write.
Then I would have "Front End" workbook or many copies of the front end workbook (i.e. one for each user,) this front end workbook would action all the interactions with your master workbook using VBA i.e when creating a new record the front end would have the forms and do all the checking , when the user hits enter the VBA in this "Front End" would open the Master workbook , check to see if anybody else has it open, if nobody has it open., it updates the Master Workbook and then closes it. In the Master Workbook open routine you can put some code that writes the userrname into a field in the workbok which can then be checked to test if the workbook is currently in use. Obviously this needs to be cleared when the workbook is saved. This can be used to send a message to a user that is trying to update when somebody already has the workbook open. Similarly when updating a record the Master workbook would need to be opened to read the record. It could then be closed, while the user updates the record, and opened again to update the record. Depending on how likely it is that the same record could updated by more that one user you might need to implement a "record locking" sceme in the master workbook . I did implement a similar system once, it wasn't bullet proof but the staff in the office found it worked perfectly, when they got a message saying " Master Workbook in use by Usernamewhoever" they just contacted whoever and when they had finished tried again. You do need to clear the Master Workbook fields when you get a PC or network crash manually.

 

[Jeffrey Mahoney]

I have created many databases in Excel. I think it's highly functional for small databases like what you're doing. I've done it a couple different ways:
1 Allow only one user at a time to access the single workbook, but close it down after no activity so other's can use it.
2 Create a form in one workbook that is Read Only that reads and saves data to one or many tables stored in other workbook(s)
3 Create a form in one workbook that is Read Only that reads and saves data to one or many tables stored in Text file(s)

Number 2 is somewhat easier. Number 3 has some coding overhead, but after the code is built it's way faster and the files are smaller. There are lots of methods used to write records to files. I would have to dig up some examples from past projects. The simple way this works is that you open the other WBs or text files as needed to read or write and then close them immediately. The code tests if the file is read only and then pauses until ready.

 

[adambc]

I think the way I would get around your problem would be to have a "Master" workbook that is password protected and can only be opened by you and any vba that you write.
Then I would have "Front End" workbook or many copies of the front end workbook (i.e. one for each user,) this front end workbook would action all the interactions with your master workbook using VBA i.e when creating a new record the front end would have the forms and do all the checking , when the user hits enter the VBA in this "Front End" would open the Master workbook , check to see if anybody else has it open, if nobody has it open., it updates the Master Workbook and then closes it. In the Master Workbook open routine you can put some code that writes the userrname into a field in the workbok which can then be checked to test if the workbook is currently in use. Obviously this needs to be cleared when the workbook is saved. This can be used to send a message to a user that is trying to update when somebody already has the workbook open. Similarly when updating a record the Master workbook would need to be opened to read the record. It could then be closed, while the user updates the record, and opened again to update the record. Depending on how likely it is that the same record could updated by more that one user you might need to implement a "record locking" sceme in the master workbook . I did implement a similar system once, it wasn't bullet proof but the staff in the office found it worked perfectly, when they got a message saying " Master Workbook in use by Usernamewhoever" they just contacted whoever and when they had finished tried again. You do need to clear the Master Workbook fields when you get a PC or network crash manually.

See my reply to Jeffrey below (you’re both suggesting similar approaches) …

 

[adambc]

I have created many databases in Excel. I think it's highly functional for small databases like what you're doing. I've done it a couple different ways:
1 Allow only one user at a time to access the single workbook, but close it down after no activity so other's can use it.
2 Create a form in one workbook that is Read Only that reads and saves data to one or many tables stored in other workbook(s)
3 Create a form in one workbook that is Read Only that reads and saves data to one or many tables stored in Text file(s)

Number 2 is somewhat easier. Number 3 has some coding overhead, but after the code is built it's way faster and the files are smaller. There are lots of methods used to write records to files. I would have to dig up some examples from past projects. The simple way this works is that you open the other WBs or text files as needed to read or write and then close them immediately. The code tests if the file is read only and then pauses until ready.

@Jeffrey Mahoney
@offthelip

Thank you for your suggestions - really got me thinking because it solves another problem ie despite being told NECER to edit data in the Master Table direct (EVERYTHING they need to do can be done via the UserForms!) Users still do it - separating them from the data stops them being even able to edit the data direct! …

Would this work (for ADD NEW - if it does I know how I can adapt it for UPDATE EXISTING)?

- separate the UserForms and the “Master” Table into two WBs
- allow Users to access the UserForm WB (WB1) only (password protect the Master Table WB (WB2) or simply don’t tell them about it!)
- change Unlocked/Locked column in the Master Table to Unlocked/UserName
- when they open WB1 refresh a Power Query which reads the Master Table into a WS in WB1 (to give them a current view of the data)
- let them add a new record using the ADD NEW UserForm
- when they click the Save Command Button, start by checking the value of the Unlocked/UserName column in the lastrow of the Master Table (WB2)
- if it's a UserName (ie <> Unlocked), display Message Box eg "UserName currently adding new record please click Save again"
- else update the value of the Unlocked/UserName column in the lastrow + 1 to UserName (making lastrow + 1 = lastrow)
- allocate the next sequential INVnnnn (incrementing the last INVnnnn in the INVENTORY ID column in the lastrow - 1)
- write the new record to (what will now be) the lastrow
- update the Unlocked/UserName column in the lastrow to Unlocked ready for the next User/new record

Have I missed anything?

 

[offthelip]

that looks OK to me, very much like what I was thinking.

 

[Jeffrey Mahoney]

You don't have to worry about locking down the DB WB. It can reside in a hidden folder. The code to open a WB, add a new record, save and close it, is all done behind the scenes. If you need to restrict a user from adding new records, just create a list, and when they try to open the master WB, just tell them they don't have the rights and to contact the administrator (you).

Remember, the Master WB is read only. The DB WB is used by everybody and needs to remain closed unless read from or written to.