Search code examples
excelvbalistobjectexcel-tables

VBA listobject won't add rows

I have a wb with several sheets, each having two tables (listobjects). I also have a userform that allows a user to add, edit, delete rows from the tables. These tables are static, ever meaning they are ever present and never deleted, they are located at the same place and never move. I also have referenced these objects in different ways, by index or by name, to see if that makes a difference.

Sometimes when a user does either an Add, Edit or Delete to any table I get the 1004 runtime error. Not sure why because I know for a fact that the object exist. After the error, excel seems to go haywire and shuts down not saving any work done. In the code below an error occurs at tbl.ListRows.Add AlwaysInsert:=True and many others like it.

I have read there are situations where excel forgets those objects are there or even forgets how many records are in a table.

I'm not sure if I'll ever get an answer as to why Excel does this...

My question is how to effectively trap for this error, and reset so excel doesn't shut down.

Private Sub pg1AddDoCode_Click()
Dim tbl As ListObject
Dim lrow As Integer

Set tbl = ThisWorkbook.Worksheets("Constants").ListObjects("DoCode")

tbl.ListRows.Add AlwaysInsert:=True

lrow = tbl.ListRows.count

With tbl.ListRows(lrow)
    .Range(1) = UCase(Me.pg1DoCode)
    .Range(2) = UCase(Me.pg1DoName)
End With

ClearValues Me.MultiPage1.Pages(1).Controls

Me.pg1AddDoCode.Enabled = True
Me.pg1EditDoCode.Enabled = False
Me.pg1DelDoCode.Enabled = False
Me.pg1Query.RowSource = tbl.Name
Set tbl = Nothing

End Sub
Solution

  • I think I know the answer:

    You are using a Listobject as a RowSource. This is as I recently found out dangerouse. Like hard crash dangerouse.

    Do the following: Before you manipulate the listobject make sure that ALL elements of your userform have the Rowsource set to "": 

    Me.pg1Query.RowSource = ""

    So the Rowsource is set to an empty string. Because if you resize the table that is the rowsource of something excel crashes.

    After the manipulation you can reset the rowsource again:

    Me.pg1Query.RowSource = tbl.name