Problem with opening multiple instances of file on a network

[dpaton05]

I have been creating several spreadsheets at work and there is a problem with opening more than 1 instance of them at the same time on a network. I have one spreadsheet called "quoting tool" that generates quotes. When quotes are finalised they are sent to separate spreadsheets called allocation sheets. The allocation sheets are for each financial year.

The problem I have is person A might have the quoting tool open that may have an entry in it within the 2019-2020 financial year. Person B might have the 2019-2020 financial year allocation sheet open on their computer. Person A is happy with the quote and press a button to transfer to the allocation sheet and it transfers to the 2019-2020 allocation sheet, save it and close it. Person B then manually adds an entry then saves it.

Because person B already had the allocation sheet open when person A made the quote and added it to the allocation sheet, saved it and closed it, if person B then makes a manual change to the allocation sheet and saves it, it will overwrite any change that person A had made.

I think I explained that. I don't know what to do to fix this, or if I can fix it. Does anyone have any ideas?

 

[Michael M]

I don't have Excel at the moment, but I'm guessing the only option available is to check, when the transfer to allocation sheet button is pressed, to see if the allocation workbook is open, and if it is display a warning to the user and maybe get them to wait x minutes before retrying.
Have a look here

https://www.mrexcel.com/forum/excel-questions/10398-vba-check-if-workbook-open.html
and also here, for on a network

https://www.ozgrid.com/forum/forum/help-forums/excel-general/62861-is-file-open-on-network