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

Why use a spreadsheet to do a databases job?

Fosco
– Fosco

2010-07-21 19:20:25 +00:00
Commented Jul 21, 2010 at 19:20 — Fosco
– Fosco, Commented Jul 21, 2010 at 19:20
the database is being built, this is a bandaid.

0bfus
– 0bfus

2010-07-21 19:21:50 +00:00
Commented Jul 21, 2010 at 19:21 — 0bfus
– 0bfus, Commented Jul 21, 2010 at 19:21

variant · Accepted Answer · 2010-07-22 00:17:07Z

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.

edited Jul 22, 2010 at 0:17

answered Jul 22, 2010 at 0:04

variant

1,3742 gold badges11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA code in spreadsheet not allowing multiple user access in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related