Code excel VBA Userform to enter data into multiple rows

Question

Please be patient with me as I try to explain this. I'm trying to develop a data entry form to take into the field with our crew that will automatically download into our desired excel sheet. We write down multiple species per point per vegetation plot. I have created a drop down species list to reduce error for each selection but have no idea how to get it to download the information across multiple rows. I can only make it work for one row (I know a forloop needs to be made but don't understand the language well enough) or how to include the location of the sample without having the user input that information. I have included what the form and excel format looks like.

Userform:

Worksheet:

Here is the code I currently have, that works for the first row of information. I would like the meter distance to automatically be inputted and I would like project ID, plot ID, and date to also be rewritten every time a new row of information is downloaded. Is this even possible? Am I overdoing it?

Private Sub CommandButton2_Click()

erow = Sheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row
    Range("a" & erow + 1) = cboProjectID.Value
    Range("b" & erow + 1) = TextBox2.Value
    Range("c" & erow + 1) = TextBox3.Value
    Range("d" & erow + 1) = cboFieldCrew.Value
    Range("e" & erow + 1) = cboAzimuth.Value
    Range("g" & erow + 1) = cboSPP1.Value
    Range("h" & erow + 1) = cboSPP2.Value
    Range("i" & erow + 1) = cboSPP3.Value
    Range("j" & erow + 1) = cboSPP4.Value
    Range("k" & erow + 1) = cboSPP5.Value
    Range("l" & erow + 1) = cboSPP6.Value
    Range("m" & erow + 1) = cboSPP7.Value
    Range("n" & erow + 1) = cboSPP8.Value

    cboProjectID.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    cboFieldCrew.Value = ""
    cboAzimuth.Value = ""
    cboSPP1.Value = ""
    cboSPP2.Value = ""
    cboSPP3.Value = ""
    cboSPP4.Value = ""
    cboSPP5.Value = ""
    cboSPP6.Value = ""
    cboSPP7.Value = ""
    cboSPP8.Value  = ""


End Sub

Answer 1

It looks like your first row of comboboxes follow a naming convention of cboSPP1 to cboSPP8
I would maybe change that to something like cboSPP1_1 to cboSPP1_8 for the first row, then cboSPP2_1 to cboSPP2_8 for the second row, etc. Then you can use a loop and something like

Me.Controls("cboSSP" & lineNum & "_" & SPPNum).Value

to access the value of the relevant drop-down.

Same sort of thing for your "meter" labels.

Untested:

Private Sub CommandButton2_Click()
    
    Dim rw As Range, lineNum As Long, SPPNum As Long, arrInfo, arrSPP, con As Control
    
    'the next unused row (in ColA)
    Set rw = ThisWorkbook.Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow
    
    'information to be repeated on each line...
    arrInfo = Array(cboProjectID.Value, TextBox2.Value, TextBox3.Value, _
                    cboFieldCrew.Value, cboAzimuth.Value)
    
    For lineNum = 1 To 9 'however many lines of info you have
        rw.Columns("A").Resize(1, UBound(arrInfo) + 1).Value = arrInfo      'assign repeated info from array
        rw.Columns("F").Value = Me.Controls("lblMeters" & lineNum).Caption  'read the "meters" label
        For SPPNum = 1 To 8 'loop the species combos for this row
            rw.Columns("G").Offset(0, SPPNum - 1) = _
                Me.Controls("cboSSP" & lineNum & "_" & SPPNum).Value
        Next SPPNum
        Set rw = rw.Offset(1) 'next worksheet row for output
    Next lineNum
    
    'clear the form
    cboProjectID.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    cboFieldCrew.Value = ""
    cboAzimuth.Value = ""
    
    'reset all SPP combos
    For Each con In Me.Controls
        If con.Name Like "cboSPP*" Then con.Value = ""
    Next con
    
End Sub