Userform to fill first empty row in named table

[Retroshift]

Hi,

I have a userform with text fields and checkboxes. The data entered in the userform will be transfered to a new row in the named table "tblColleagues". This table already contains a few rows with data but the named table range also covers lots of empty rows. How is it possible to make the userform data being entered in the first entirely empty row starting from the top below the header to the bottom of the named table? The code below only adds new rows after the bottom of the named range, ignoring all the empty rows within the table itself.
The data input of "fullparttime" should also be limited to a number =>0<=100 as this is a percentage.
Anyone has an idea on how to alter the code below accordingly?

Sub cbAdd_Click()
Dim firstname As String
firstname = txtFirstName.Text

Dim lastname As String
lastname = txtLastName.Text

Dim shortname As String
shortname = txtShortName.Text

Dim fullparttime As String
fullparttime = txtFullparttime.Text 'incorrect data type? percentage

Dim wsh As Worksheet
Set wsh = ThisWorkbook.Worksheets("List")

Set tbl = wsh.ListObjects("tblColleagues")

Dim newRow As ListRow
Set newRow = tbl.ListRows.ADd  'positioning needs to loop within the named table "tblColleagues" until the first empty row, starting from the first row below the header

With newRow
.Range(1) = firstname
.Range(2) = lastname
.Range(3) = shortname
.Range(5) = fullparttime 'Condition: numeric =>0<=100 as this is a percentage

If cbx1.Value = True Then
.Range(4) = "x"
End If

If cbx2.Value = True Then
.Range(6) = "x"
End If

End With

End Sub

 

[Retroshift]

My bad. The code of post #11 was not very clear as I only have basic VBA knowledge.
My intentions are to allow the percentage field to be left either empty, or a number =>0<=100 (so indeed no text data is allowed).

 

[igold]

I had to change a couple of things, the easiest thing to do is to repost what I now have. I have tested with fullparttime = 1) Empty, 2) Text, 3) Numeric. The code did not error for me under any these scenarios. I had been thinking that the code would not be run without that field having a value.

Private Sub cbAdd_Click()
    Dim r As Long, c As Long, i As Long
    Dim firstname As String, lastname As String, shortname As String
    Dim fullparttime
    Dim wsh As Worksheet: Set wsh = ThisWorkbook.Worksheets("List")
    Dim tbl As ListObject: Set tbl = wsh.ListObjects("tblColleagues")
   
    firstname = txtFirstName.Text
    lastname = txtLastName.Text
    shortname = txtShortName.Text
    fullparttime = txtFullparttime
    If fullparttime <> "" Then
        fullparttime = CSng(txtFullparttime.Text)  'incorrect data type? percentage
        fullparttime = fullparttime / 100
    End If
    For r = 1 To tbl.ListRows.Count
        For c = 1 To tbl.ListColumns.Count
            If tbl.ListRows(r).Range.Formula2(1, c) <> "" Then
                Exit For
            End If
            If c = tbl.ListColumns.Count Then i = r
        Next
        If i > 0 Then Exit For
    Next
    If r > tbl.ListRows.Count Then
        tbl.ListRows.Add AlwaysInsert:=True
        r = tbl.ListRows.Count
    End If
        With tbl.ListRows(r)
            .Range(1) = firstname
            .Range(2) = lastname
            .Range(3) = shortname
            If Not fullparttime = "" Then
                .Range(5) = FormatPercent(fullparttime) 'Condition: numeric =>0<=100 as this is a percentage
            End If
    If cbx1.Value = True Then
    .Range(4) = "x"
    End If
   
    If cbx2.Value = True Then
    .Range(6) = "x"
    End If
   
    End With

End Sub
Private Sub txtFullparttime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If txtFullparttime.Value = "" Then Exit Sub
    If Not IsNumeric(txtFullparttime.Value) Then
        txtFullparttime.Value = ""
        txtFullparttime.SetFocus
        Cancel = True
    End If
End Sub

 

[Retroshift]

Hi igold, this seems to do the trick. One last thing: could an error message box be implemented if the percentage field value is non-numeric and/or not a value between 0 and 100?

 

[igold]

You can change the sub "Private Sub txtFullparttime_Exit(ByVal Cancel As MSForms.ReturnBoolean)" to this:

Private Sub txtFullparttime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If txtFullparttime.Value = "" Then Exit Sub
    If Not IsNumeric(txtFullparttime.Value) Or txtFullparttime.Value < 0 Or _
        txtFullparttime.Value > 100 Then
        txtFullparttime.Value = ""
        txtFullparttime.SetFocus
        Cancel = True
        MsgBox "This Value Must Be A Number (No Text)" & _
            vbNewLine & "Which Is Greater Than ""0"" and Less Than ""100""", vbCritical, "INVALID ENTRY"
    End If
End Sub

 

[Retroshift]

This worked like a charm. I have marked your solution as solved. Thank you so much for your time, knowhow and patience.

 

[igold]

You're welcome. I was happy to help. It took a while but we got it done. Thanks for the feedback!!