Userform for data entry

Hi, I have a userform where staff will go into "Add case" tab to enter data. After selecting Sample Type, entering Marking and Mortuary No, they will click "Add". This command will add the data into textbox16. One problem here is that the "Case assigned" number should remain as "DVI-00001" if "Add" command is clicked, such that the next data added into textbox16 will have "DVI-00001-002". The "Case assigned" number should change to "DVI-00002-001" if the "Prelog" command is clicked. I'm having problems trying to make that happen.

Staff will carry on adding to the textbox based on how much data there is, after which, they will click Prelog. This command will add the data into the worksheet. I'm having problems trying to get the data from textbox16 into the worksheet. The data needs to be split into the respective rows and columns according to the same heading.

Any help is appreciated

Your requirements are not entirely clear, but give these changes a try and let me know how it goes.

Form initialize code:

Private Sub UserForm_Initialize()
    MultiPage1.Value = 0
    Dim cUnique As Collection
    Dim rng As Range
    Dim cell As Range
    Dim vNum As Variant
    Dim i As Integer
    Dim strW As String
    Dim sh As Worksheet
    Dim lastrow As Long
    
    
    lastrow = Worksheets("Case Log").Range("A" & Rows.Count).End(xlUp).Offset(0).Row
    If lastrow > 1 Then
        Me.TextBox13.Value = "DVI-" & Format(Split(Cells(lastrow, "A"), "-")(1) + 1, "00000") & "-" & Format(1, "000")
    Else
        Me.TextBox13.Value = "DVI-" & Format(lastrow, "00000") & "-" & Format(lastrow, "000")
    End If
    Me.TextBox13.Enabled = False
    
    ...
    ...

Add button code:

Private Sub CommandButton12_Click()
TextBox16.MultiLine = True
With TextBox16
    .Text = .Text & vbCr & TextBox13.Text & " " & ComboBox6.Text & " " & TextBox6.Text & " " & TextBox15.Text
End With
ComboBox6.ListIndex = -1
TextBox6.Text = vbNullString
TextBox15.Text = vbNullString
TextBox13.Value = "DVI-" & Format(Split(TextBox13.Value, "-")(1), "00000") & "-" & Format(Split(TextBox13.Value, "-")(2) + 1, "000")
End Sub

Prelog button code:

Private Sub CommandButton3_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Dim myLines As Variant


    Set ws = Worksheets("Case Log")
     
    myLines = Split(TextBox16.Text, vbCr)
    
     'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
     
     'copy the data to the database
    ws.Cells(iRow, 1).Value = Me.TextBox13.Text
    ws.Cells(iRow, 2).Resize(1 + UBound(myLines), 1).Value = myLines
    ws.Cells(iRow, 5).Value = Me.TextBox4.Text
    
    TextBox13.Value = "DVI-" & Format(Split(TextBox13.Value, "-")(1) + 1, "00000") & "-" & Format(1, "000")
    
    Me.TextBox14.Text = "NULL"
    Me.TextBox14.Enabled = False
    
End Sub

Try these changes:

Private Sub CommandButton12_Click()
TextBox16.MultiLine = True
With TextBox16
    If .Text <> "" Then
        .Text = .Text & vbCr & TextBox13.Text & " " & ComboBox6.Text & " " & TextBox6.Text & " " & TextBox15.Text
    Else
        .Text = TextBox13.Text & " " & ComboBox6.Text & " " & TextBox6.Text & " " & TextBox15.Text
    End If
End With
ComboBox6.ListIndex = -1
TextBox6.Text = vbNullString
TextBox15.Text = vbNullString
TextBox13.Value = "DVI-" & Format(Split(TextBox13.Value, "-")(1), "00000") & "-" & Format(Split(TextBox13.Value, "-")(2) + 1, "000")
End Sub

Private Sub CommandButton3_Click()
    Dim iRow As Long, i As Long
    Dim ws As Worksheet
    Dim myLines As Variant, v


    Set ws = Worksheets("Case Log")
     
    myLines = Split(TextBox16.Text, vbCrLf)
    
     'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
     
     'copy the data to the database
    For i = 0 To UBound(myLines)
        v = Split(myLines(i), " ")
        ws.Cells(iRow, 1).Resize(, 4).Value = Array(v(0), v(1), v(3), v(2))
        ws.Cells(iRow, 5).Value = Me.TextBox14.Text
        iRow = iRow + 1
    Next
    TextBox16.Text = ""
    
    TextBox13.Value = "DVI-" & Format(Split(TextBox13.Value, "-")(1) + 1, "00000") & "-" & Format(1, "000")
    
    Me.TextBox14.Text = "NULL"
    Me.TextBox14.Enabled = False
    
End Sub

Edit:
It will not populate properly if you choose "Body Part", since it contains a space.
You can try a different column separator, like underscore ( _ )
or revamp your form and use multi-column listbox instead of a multi-line textbox.

You need the With statement block, don't remove them.

Private Sub CommandButton12_Click()
TextBox16.MultiLine = True
With TextBox16
    If .Text <> "" Then
        .Text = .Text & vbCr & TextBox13.Text & " " & ComboBox6.Text & " " & TextBox6.Text & " " & TextBox15.Text
    Else
        .Text = TextBox13.Text & " " & ComboBox6.Text & " " & TextBox6.Text & " " & TextBox15.Text
    End If
End With

Oh silly me... Thanks for correcting. I attached a revised version of the previous workbook. After users have filled in info, clicked "Add" and clicked "Receive in lab", how do I code the "transfer custody" button such that what is in textbox16 will have the correct custodian? example:
Textbox16 has these info
Case: DVI-00001-001 Sample Type: Bone Marking: 1234 Mortuary ID: M1234
Case: DVI-00001-002 Sample Type: Body part Marking: 1235 Mortuary ID: M1235
Clicking "Receive in Lab" will transfer these info into respective columns in worksheet. Column E will be populated with textbox14.
Now users need to transfer the custody to another person or location. They do so by selecting from combobox5 and click "Execute". Column F should populate the new custodian for DVI-00001-001 and DVI-00001-002

Try this change:

Private Sub CommandButton10_Click()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim lastrow As Long
    Dim r As Long, v, i As Long
     
    Set ws = Worksheets("Case Log")
    
    v = Split(TextBox16.Text, vbCrLf)
    
    lastrow = ws.Range("A1000").End(xlUp).Row
    For i = 0 To UBound(v)
        For r = 2 To lastrow
    
            If ws.Cells(r, 1) = Split(v(i), " ")(0) Then
    
                ws.Cells(r, 6).Value = ComboBox5.Value
                 
            End If
        Next r
    Next
    Application.ScreenUpdating = True
    
    TextBox14.Text = ComboBox5.Value
    ComboBox5.Value = ""

End Sub

Hi, you are deviating further from the original topic with subsequent problems/questions.

I suggest closing this thread and opening a new one with your new problem. Fresh threads usually get help faster, and possibly better solutions too.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

If you think that I could be of help, you can PM me a link to your new thread containing the new question, and I will take a look when time permits.