Hi
I have a file that can be used by 5 people, it's read only has SQL data in it so it cannot be shared. I'm using Sub Worksheet_Change(ByVal Target As Range) to detect an entry, which will then open a file to save the comments in, add the comment to the last line close and save the file (all of this takes less than 1 second) and is working fine. When writting it I knew it may be an issue if people enter a comment in at the same time, but thought this wouldn't happen very often. I was very wrong. Only the VBA code can access the file that holds the comments, so no one will have this open.
What I'm after is for the sub below to check if the file can be opened as write, but if not wait 1 second and check again on a loop, with a count of 5 so it's not an inifite loop. I cannot get it to do this, any help would be appreciated.
I have a file that can be used by 5 people, it's read only has SQL data in it so it cannot be shared. I'm using Sub Worksheet_Change(ByVal Target As Range) to detect an entry, which will then open a file to save the comments in, add the comment to the last line close and save the file (all of this takes less than 1 second) and is working fine. When writting it I knew it may be an issue if people enter a comment in at the same time, but thought this wouldn't happen very often. I was very wrong. Only the VBA code can access the file that holds the comments, so no one will have this open.
What I'm after is for the sub below to check if the file can be opened as write, but if not wait 1 second and check again on a loop, with a count of 5 so it's not an inifite loop. I cannot get it to do this, any help would be appreciated.
VBA Code:
Sub Open_Comments()
Set Parts = Workbooks.Open(Filename:="***\Expediting Comments - File is locked.xlsm", Password:="***")
End Sub