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]

Hey igold.
I deleted the 2 in "Formula2" (see VBA line quoted in post #10) and for some reason or another, it seems to add the data correctly.
If the percentage text field (range 5) is non-numeric or left blank however, runtime error 13 appears. Any idea on how to limit the entry of the percentage text field with a loop until it is an integer =>0<=100?
I tried to add this code but it does not do the job:

'...
.Range(5) = fullparttime
    Do
    If Not IsEmpty(fullparttime) Then
        If IsNumeric(fullparttime) >= 0 <= 100 Then
        End If
    End If
    Loop Until True
'...

 

[igold]

How about this in your form module

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

 

[Retroshift]

How about this in your form module

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

I added this in the form module and it throws a type mismatch (Error 13) on the line "fullparttime = txtFullparttime.Value / 100" in the Private Sub cbAdd_Click()

 

[igold]

Did you add it as a separate sub or combine in with the other code. It does not throw the error for me...

 

[Retroshift]

A separate private sub underneath the private sub of post #6

 

[igold]

That is exactly where it is on my sheet and it does not throw an error and works as per your requirement.
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 As Single
    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.Value / 100   'incorrect data type? percentage

    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
            .Range(5) = FormatPercent(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
Private Sub txtFullparttime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsNumeric(txtFullparttime.Value) Then
        txtFullparttime.Value = ""
        txtFullparttime.SetFocus
        Cancel = True
    End If
End Sub

 

[Retroshift]

Indeed. Might there be any workaround to avoid the "fullparttime = txtFullparttime.Value / 100"? Like declare it to something other than .Value?
The mismatch error could maybe have something to do with the way percentages are read in this region?
I have Excel 2019 Plus if that info might be of any help.

 

[igold]

This works for me, does it work for you...

    firstname = txtFirstName.Text
    lastname = txtLastName.Text
    shortname = txtShortName.Text
    fullparttime = CSng(txtFullparttime.Text)  'incorrect data type? percentage
    fullparttime = fullparttime / 100

 

[Retroshift]

It works if data is entered in the percentage field of the userform; but returns the same error 13 when this field is empty.
Maybe "empty" cannot be divided by 100?

 

[igold]

I am confused, I thought you were writing code to prevent that field from being empty or containing text. as shown in your Post #11