Data overrid in a shared workbook???? help :-)

[ciapul12]

Hi All
Hopefully I can find the answer to may problem here...
I have developed this program that allow users to create new reports and log the information into the main data base, few days ago I was showing someone how to use it and we have created 5 new reports 50,51,52,53,54 and 55 all reports had been recorded successfully into the data base apart from report No. 54 and 55 , what actually happened was some else was using this program at the same time we were using it and his reports override our reports .
I should mention that we're using workbook in shared mode and I thought that there's no way that data in shared workbook can't be override when used by 2 different users at the same time.
Please take a look at the below code from my userform
Thank for you help in advance.

Please Login or Register  to view this content.

[Leith Ross]

Hello ciapul12,

Here a few links about Shared Workbooks. This should help you better understand the limitations of using a shared workbook.

About Shared Workbooks

Share a Workbook

Avoid Shared Workbooks in Excel

[ciapul12]

Hello ciapul12,

Here a few links about Shared Workbooks. This should help you better understand the limitations of using a shared workbook.

About Shared Workbooks

Share a Workbook

Avoid Shared Workbooks in Excel

Hi Leith
Thanks for the links it was very informative I must say :-)
You see in my case the code looks for the next available/empty row and then assigns the id number to the report so If the last report was 50 the next will be 51 and so on, so I still don't understand how the override of the data could happen???
Is there anything I could add to my code that would work the same as when you close/save and re-open workbook? some sort of refresh?
I think what may have happened was 1 user opened the workbook the last report was 50 then other user have opened the same workbook because the user 1 didn't closed and saved the workbook user 2 still had last report as 50 instead 52 and this is why override occurred?

Thank you.

[LJMetzger]

Hi ciapul12,

Your problem is similar to, but not the same as the following link:
http://www.excelforum.com/excel-prog...in-msgbox.html

In post #8 and #9 in that thread, the poster found a solution to his problem, by using an auxiliary Excel file.

You may be able to do something similar when you want to write to the shared workbook:
a. Attempt to open a pre-existing dummy single User Excel file.
b. If the file is successfully opened, you can now write to the original Excel file.
c. If the file is NOT successfully opened, it means the original Excel file is being updated by someone else
and appropriate action must be taken.
d. Close the dummy single User Excel file when when write has completed.

Lewis