Method 'Add' of object 'ListRows' failed (Run-time error '-2147417848 (80010108)':

[mdever74]

I'm at wits end...

I have two ActiveX control buttons on a Worksheet ("Servers_Test"): btnAddWindowsLinux and btnDeleteWindowsLinux.

Here are the following VBA macros, respectively:

Option Explicit


Private Sub btnAddWindowsLinux_Click()

Dim servers As Worksheet
Dim serverTable As ListObject
Dim serverTableRow As ListRow
Dim serverTableRows As ListRows

Set servers = ThisWorkbook.Worksheets("Servers_Test")
Set serverTable = servers.ListObjects("WindowsLinux")
Set serverTableRow = serverTable.ListRows.Add(AlwaysInsert:=True) ' Reference line where error(s) are occuring for adding a row to the table

If serverTable.ListRows.Count > 1 Then
    btnDeleteWindowsLinux.Enabled = True
End If


End Sub



Private Sub btnDeleteWindowsLinux_Click()

Dim servers As Worksheet
Dim serverTable As ListObject
Dim serverTableRow As ListRow
Dim serverTableRows As ListRows

 Set servers = ActiveWorkbook.Worksheets("Servers_Test")
 Set serverTable = servers.ListObjects("WindowsLinux")
 Set serverTableRows = serverTable.ListRows
 
If serverTableRows.Count > 1 Then
    serverTable.ListRows(serverTable.ListRows.Count).Delete ' Reference line where error(s) are occuring for adding a row to the table
        If serverTable.ListRows.Count > 1 Then
            btnDeleteWindowsLinux.Enabled = True
        Else
            btnDeleteWindowsLinux.Enabled = False
        End If
    
    End If

This is a completely random error message that appears. Even if I add multiple rows, then delete them (using the control buttons), then go to add more, I get the Run-time error '-2147417848 (80010108)': "Method 'Add' of object 'ListRows' failed" window. Following a "Debug" selection (and having VBA identify the line of code that is causing the error), when I stop the macro and press the "Add New Server" button (btnAddWindowsLinux), I get a new error message: "Run-time error '1004': Application-defined or object-defined error".

I've been to NUMEROUS Excel help sites with absolutely no luck. Can anyone please help me eliminate these random, inconvenient, and unproductive errors from occuring?

Thank you, in advance, for any assistance you can provide.

- Matt

 

[Sektor]

One of the craziest assumption about your error is that Excel can't shift cells beneath it.
Anyway, the example file would shed some light.

 

[mdever74]

I do appreciate your quick response, Sektor.

I wouldn't be able to share it as it is a sensitive document for my job. I will say, however, that the "WindowsLinux" table is the only one on the spreadsheet. The formulas (in their respective cells) reference a second sheet called "Sheet2" (because I'm so original ). This table also includes conditional formatting and data validation configurations. I'm not sure if that is posing a problem, but the fact that the macro works a few times and then breaks is what is troubling me.

 

[Sektor]

Well, it's hard to say. I checked out on my sample List - and everything works without error.
You need to use "exclusion method": first, you remove one thing - say, conditional formatting - and work with table. If it doesn't error out, then the troubles were with conditional formatting. If it errors, then you remove formulas. If no error occurs, then the troubles were with formulas et cetera. Good luck!

 

[mdever74]

So here in lies the dilemma...

Removing the data validation and formulas, resulted with no more errors. However, I need the data validation and formulas as, without these, it renders the table's purpose moot.

Having said all this, what is the resolve to eliminating those errors while maintaining data validation cells as well as the formulas?

Thanks, again.

- Matt