VBA code in spreadsheet not allowing multiple user access in excel.

Question

I have a spreadsheet that one department will scan a sample into and it will time and date stamp in the column next to it when it was scanned (sent). Then the next department will scan the sample into another column and it will time and date stamp when it was scanned (received). the code i am using is below, but because of the protection, i cannot have the spreadsheet edited by two people at the same time. is there anything i can do to allow this?

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Protect ("Password"), UserInterfaceOnly:=True

'Only write a timestamp of an odd column changes (because the timestamps go in the even columns)
If Target.Column Mod 2 > 0 Then

    'Get the first part of the address, to get the actual column being changed
    Dim columnAddress As String
    columnAddress = Target.Address

    If InStr(columnAddress, ":") > 0 Then
        columnAddress = Left(columnAddress, InStr(columnAddress, ":") - 1)
    End If

        If Not ActiveSheet.Range(columnAddress).Formula = "" Then

        'Write the timestamp for the previous column
        ActiveSheet.Range(columnAddress).Offset(0, 1).Formula = Now

        Else
            ActiveSheet.Range(columnAddress).Offset(0, 1).Formula = ""
        End If
End If

End Sub

Answer 1

I highly recommend that you use ADO here. If you go that route, you would have a remote Excel workbook (or .mdb, if you so please) as your database and the "child" workbooks (or Excel add-ins) would write the relevant data to this database and, if required, also read from it.

A good overview of ADO in VBA is provided here: http://www.xtremevbtalk.com/showthread.php?t=217783.