PDA

View Full Version : VBA Code to check if shared document is locked for editing



chaz5687
03-18-2011, 05:07 AM
Hello

I am pretty new to the whole vba thing and everything I have learnt has been trial and error and from forum's such as these so bare with me if this is a simple issue.

I have a shared workbook that at the moment only has two users (looking to expand to 4-5 if I can fix the current issue). The spreadsheet needs to be open in both locations all day. User1 inputs all but one field of the data. User two monitors these changes and inputs the data into the other field. At no point should there be any conflict with data in a cell. I have a Macro that runs every 30seconds which in turn runs a couple of other Macro's for different functions. One of these saves the workbook to allow the changes to be seen be both users in near realtime.

This all works fine 99% of the time. The problem I am getting is on the very rare occasion when they both open the worksheet at exactly the same time. It means the code is running at the same time and they both try to save at the same time. This causes one of them to get a 'document locked for editing' message. After that point they no longer update together. Because the area's using this sheet are on different floors, and different area's to myself I cannot monitor if this happens. Is there a function similar to the following?

If document is locked for editing then
Wait 2 seconds (I know how to do this using appplication.ontime)
save
else
save
end if

If not then is there an error code for the message that appears so I can use error handing? I have spent a few hours trying to figure this out for myself and am getting no where. Any help would be greatly appreciated.

Charles