PDA

View Full Version : Temp Lock Workbook while form data is submitted to worksheet



mykal66
06-17-2015, 11:49 PM
Morning guys.

I have set up form for several members of a team to enter and submit data to a hidden worksheet. When the user submits the data i need it to check and see if someone else is already writing to the worksheet and display a message box advising them to try again in a few minutes, exit the macro and return to the completed form.

I've used some code to do this many times in a previous job without any issues using this code but this doesn't work now i am using a newer version of excel (2010)


Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Dim strFileName As String
Set ws = Worksheets("MasterList")

strFileName = ThisWorkbook.FullNameURLEncoded
If FileLocked(strFileName) Then
MsgBox ("This file is currently being saved, please try again in a few moments.")
Exit Sub
End If

ActiveWorkbook.Save

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


I have been on the internet and found some more code which uses a separate module and a few line in the main code but as soon as i test with a couple of users it crashes and the form data is lost or the the data tries to overwrite and existing row of data.

Does anyone know why the code above no longer works please?

Thanks