Sharing excel file on LAN

I have an excel file with a userform which is used to input data.

Whenever the userform is initialized, a sr No(In a textbox) is automatically generated based on the max value in column A.The user then enter other data as per the fields in the userform.

When the submit(Command button) is pressed the data is enter in an excel sheet of the same workbook.

I want to share this workbook over my LAN and allow different users to edit at the same time.

The problem is when two users are accessing the workbook at the same time,the Sr No generated is same for both of them.

Now when user 1 saves the file the data is recorded in the worksheet, but when user 2 saves the file, the data gets overwritten and only what user 2 has recorded gets saved.

How to overcome this problem.

Workbook Attached.

Hello karthikcoep,

The way to do this is keep the serial number in a separate file. This provides exclusive user access as only one user can have the file open at a time. It also allows all users to read and write the same serial number. The serial number file is a single line text file that can be stored on the server. Create the file using NotePad and enter the current serial number and save the file where you and others can access it. Change the string variable SNfile in the macro to the path of this file (include the file name and extension with the path). Here are the 2 macro to get the serial number and update it. The text box for the serial number has its Locked property set to True. This allows only the code to change the serial number and not the user.

Function GetSN()

  Dim FSO As Object
  Dim SN As Double
  Dim SNfile As Variant
  Dim TS As Object
  
    SNfile = "C:\Documents And Settings\Admin.ADMINS\My Documents\Query Serial Number.txt"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set TS = FSO.OpenTextFile(SNfile, 1, True, -2)
    
      GetSN = TS.ReadLine
    
    TS.Close
    Set FSO = Nothing
    
End Function

Sub UpdateSN()

  Dim FSO As Object
  Dim SN As Variant
  Dim SNfile As String
  Dim TS As Object
  
    SNfile = "C:\Documents And Settings\Admin.ADMINS\My Documents\Query Serial Number.txt"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
      Set TS = FSO.OpenTextFile(SNfile, 1, False, -2)
        SN = TS.ReadLine
      TS.Close
      
      Set TS = FSO.OpenTextFile(SNfile, 2, False, -2)
        TS.WriteLine SN + 1
      TS.Close
      
    Set FSO = Nothing
    Set TS = Nothing

End Sub