Run Time Error '91' When adding userform data to table

Question

I'm trying to take simple data entered into two text boxes within a userform and then have this data added to a table named "AvantAct". I want the data entered into the first blank row in the table every time the userform is run. Interestingly, the first time I did this it worked flawlessly. However after exiting the workbook and then coming back to it later I seem to get: -

Run time error '91': Object variable or With block variable not set.

When I debug the following line is highlighted:

tbl.DataBodyRange(lrow2, 1). Value = Me.TxtDate.Value

I should add that my table is currently an empty (freshly inserted) table. It has 8 columns (with headers), one empty row (from being inserted) and a total row.

Any suggestions?

Private Sub SubmitButton_Click()

Dim ws As Worksheet
Dim tbl As ListObject
Dim TxtDate As Date
Dim TxtPmt As Currency
Dim col As Integer
Dim lrow As Range
Dim lrow2 As Long


Set ws = ActiveWorkbook.Worksheets("Avant")
Set tbl = ws.ListObjects.Item("AvantAct")

If tbl.ListRows.Count > 0 Then
    Set lrow = tbl.ListRows(tbl.ListRows.Count).Range
    For col = 1 To lrow.Columns.Count
        If Trim(CStr(lrow.Cells(1, col).Value)) <> "" Then
            tbl.ListRows.Add
            Exit For
        End If
    Next col
End If

lrow2 = tbl.ListRows.Count
tbl.DataBodyRange(lrow2, 1).Value = Me.TxtDate.Value
tbl.DataBodyRange(lrow2, 3).Value = Me.TxtPmt.Value

Unload Me

End Sub

Is there more code that isn't here? It works for me the difference is that I didn't have `Me.TxtDate.Value` when it stops there, does the immediate windows show you the value? `?Me.TxtDate.Value` — Gary Evans, Jun 06 '16 at 15:30
The only other code is setting the default value and focus for the txtboxes. And no, it does not show the value — Aaron, Jun 06 '16 at 15:48

score 0 · Accepted Answer · answered Jun 06 '16 at 19:51

The issue comes from having an empty table to begin with.

If tbl.ListRows.Count > 0 Then
    Set lrow = tbl.ListRows(tbl.ListRows.Count).Range
    ...
End If

Because the count was not greater than zero (it was zero) lrow was never set, hence the error.

If tbl.ListRows.Count = 0 Then
    tbl.ListRows.Add
else
    Set lrow = tbl.ListRows(tbl.ListRows.Count).Range
    ...
End If

In addition, your question asks: -

I want the data entered into the first blank row

The code is not doing this, the code is checking only the last row, and adding a row if it wasn't already empty, so in a list where out of 5 rows, the third row was empty, the third row would not be used but a row at the bottom would be added instead. The below would do as you expect: -

Private Sub SubmitButton_Click()
Dim ws          As Worksheet
Dim tbl         As ListObject
Dim TxtDate     As Date
Dim TxtPmt      As Currency
Dim col         As Integer
Dim lrow        As Range
Dim lrow2       As Long
Dim BlnYesNo    As Boolean

Set ws = ActiveWorkbook.Worksheets("Avant")
Set tbl = ws.ListObjects.Item("AvantAct")

If tbl.ListRows.Count = 0 Then
    tbl.ListRows.Add
    lrow2 = 1
Else
    For lrow2 = 1 To tbl.ListRows.Count
        Set lrow = tbl.ListRows(lrow2).Range

            'If it stays true, we must add a row
            BlnYesNo = True

            For col = 1 To lrow.Columns.Count
                If Trim(CStr(lrow.Cells(1, col).Value)) <> "" Then
                    BlnYesNo = False
                    Exit For
                End If
            Next

            If BlnYesNo Then Exit For

        Set lrow = Nothing
    Next

    'If its false then all rows had data and we need to add a row
    If Not BlnYesNo Then
        tbl.ListRows.Add
        lrow2 = tbl.ListRows.Count
    End If

End If

tbl.DataBodyRange(lrow2, 1).Value = "A"
tbl.DataBodyRange(lrow2, 3).Value = "B"

Set tbl = Nothing
Set ws = Nothing

End Sub

Aahh there we go. This works exactly as I was hoping. I had actually narrowed it down to the same root cause you pointed out but hadn't got it fully fixed yet. Thanks for the excellent work! — Aaron, Jun 06 '16 at 22:10
Now, is there a way to reference a value in a cell in one of the rows that was created this way? For example if I ran through this process and added one (the only one) row to my table. Then I wanted to add another row using the same process with the exception that instead of using a txtbox value I wanted to use a value from a cell in the original row. — Aaron, Jun 09 '16 at 15:01
You could use a static variable (declare as `Static` instead of `dim`) to remember the last row and use it that way. If you get stuck start a new question so we can see how far you got :) — Gary Evans, Jun 10 '16 at 07:26

Run Time Error '91' When adding userform data to table

1 Answers1