1

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
braX
  • 11,506
  • 5
  • 20
  • 33
Brad
  • 23
  • 5
  • I tested your code and I'm not getting that error at all. It works fine for this limited example. – PeterT Oct 26 '18 at 16:19
  • I don't always get it either. It's very random. The code works for a while then all of a sudden 1004 error. Like the object doesn't exist. Very frustrating. – Brad Oct 26 '18 at 16:41
  • I believe you need to create a row object. Dim addedRow as ListRow, then Set addedRow = tbl.ListRows.Add AlwaysInsert:=True, then use addedRow to manipulate ranges – Guest Oct 26 '18 at 17:53
  • @brad My answer solved the issue then? – Lucas Raphael Pianegonda Nov 05 '18 at 15:50
  • @LucasRaphaelPianegonda...Yes! I was giving myself some time to test more. But I haven't had any issues since applying your solution – Brad Nov 26 '18 at 01:22

3 Answers3

0

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
Lucas Raphael Pianegonda
  • 1,151
  • 12
  • 29
0

Late to the party, but my own problem was that I had set the sheet scroll area to the dimensions of the table to prevent users from scrolling into data I wanted hidden. This unfortunately prevents a row being added to the table. Code like this overcomes the problem (sWks holds name of worksheet):

ThisWorkbook.Worksheets(sWks).ScrollArea = ""    'Unlock table scroll to add a row

With ThisWorkbook.Worksheets(sWks).ListObjects(1)

    .ListRows.Add AlwaysInsert:=True

End With

'Write to the added row, then lock scroll area to table again using:

With ThisWorkbook.Worksheets(sWks)              
    .ScrollArea = .ListObjects(1)

End With
David Buck
  • 3,752
  • 35
  • 31
  • 35
Old Fogey
  • 1
  • 1
0

I had this issue too. Referring to @Lucas Raphael Pianegondas answer, instead of manipulating the rowsource the code below helped me to solve the problem:

tbl.ListRows.Add Position:=1, AlwaysInsert:=True

I haven't tried different positions yet.

The hint is originally from here: https://social.msdn.microsoft.com/Forums/office/en-US/6fbcfa0f-e1d0-49e9-b96d-7c390fcf61a2/method-add-of-object-listrows-failed?forum=exceldev

Sergeij_Molotow
  • 83
  • 7