populating the next available row in a shared workbook

[Blanchetdb]

Hi

I have a shared workbook that is used by multiple individuals at the same time. It is my first time using a shared workbook and I need for individuals to be able to enter their data via a UserForm where the data populates the next available row in a common worksheet. I presently have Macros that will populate the next available row but if two individuals are in the workbook at the same time, the data goes to the same row and a message appears asking one of the users to either accept their data or the other user's data.

this is the UserForm:

and this is the macro linked to that form:

Private Sub CommandButton1_Click()
'when we click the continue button
Dim TargetRow As Integer
Dim TargetRow1 As Integer
Dim FullName As String 'full name
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long

      

If Sheets("Engine").Range("A3").Value = "NEW" Then

TargetRow = Sheets("Engine").Range("A2").Value + 1

Else

TargetRow = Sheets("Engine").Range("A4").Value

End If



''START INPUT IN DATABASE''

Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 0).Value = TestGRLV
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 1).Value = TextStartDate
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 2).Value = TextBox1
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 3).Value = TestArea
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 4).Value = TextBox20
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 5).Value = TestNumber
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 7).Value = TextBox7
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 8).Value = TextBox8
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 9).Value = TextBox16
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 10).Value = ComboBox5
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 11).Value = ComboBox6
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 12).Value = ComboBox4
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 13).Value = ComboBox3
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 14).Value = TextBox15
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 15).Value = TextBox18
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 16).Value = TextBox17
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 17).Value = TextBox10
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 18).Value = TextBox19
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 19).Value = TestStatus
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 20).Value = TextBox5
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 22).Value = TextBox6
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 23).Value = ComboBox7
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 24).Value = TextBox11
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 25).Value = TextBox13
Sheets("Staffing-Processes").Range("Ref").Offset(TargetRow, 26).Value = TextBox12



'Copy to Active Cases

If Sheets("Engine").Range("A3").Value = "NEW" Then

TargetRow1 = Sheets("Engine").Range("A10").Value + 1

Else

TargetRow1 = Sheets("Engine").Range("A12").Value

End If


Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 0).Value = TestGRLV
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 1).Value = TextStartDate
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 2).Value = TextBox1
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 3).Value = TestArea
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 4).Value = TextBox20
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 5).Value = TestNumber
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 7).Value = TextBox7
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 8).Value = TextBox8
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 9).Value = TextBox16
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 10).Value = ComboBox5
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 11).Value = ComboBox6
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 12).Value = ComboBox4
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 13).Value = ComboBox3
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 14).Value = TextBox15
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 15).Value = TextBox18
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 16).Value = TextBox17
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 17).Value = TextBox10
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 18).Value = TextBox19
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 19).Value = TestStatus
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 20).Value = TextBox5
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 22).Value = TextBox6
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 23).Value = ComboBox7
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 24).Value = TextBox11
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 25).Value = TextBox13
Sheets("Science-Staffing").Range("RefScience").Offset(TargetRow1, 26).Value = TextBox12

 

''END INPUT IN DATABASE''

If Sheets("Engine").Range("A3").Value = "NEW" Then

MsgBox FullName & " has been added.", 0, "Sucessful"

Else

If Sheets("Engine").Range("A3").Value = "EDIT" Then

MsgBox FullName & " has been edited.", 0, "Sucessful"

Else

If Sheets("Engine").Range("A3").Value = "DELETE" Then

MsgBox "Userhas been deleted.", 0, "Sucessful"

End If
End If
End If



Unload TextAdvior


End Sub

Private Sub CommandButton2_Click()
'when we click the cancel button

Unload TextAdvior

End Sub

The macros do what it is intended to do but my issue is when two individuals are in the workbook at the same time, their data populates the same cells.

Is there a way to fix this?

thank you
Dan

 

[rondeondo]

Hi Dan,
My first thought with this is to add something to the next available row at the moment that the form is opened and then use a variable to store that row number, when the form is submitted use that row. That way if a second user opens the form moments after the first, the next available row for them will leave the first one. Your code already looks for the next available row so it's a minor addition to pre-populate that row at the point of opening the form.