Hi, This is my first post - registered yesterday. I fully expected to be asking for help but figured it out this morning and wanted to share.

Problem: Attempting to add a row to an Excel table via module code that is invoked from a command button on user form. Excel crashes.

Using ListObject.ListRows.Add method

Error: "The object invoked has disconnected from its clients" - followed by Excel crashing in ill humor.

Description.

My application has a combo box and list box on a user form.
  • Combo box has a list of product types populated from Excel table via RowSource of combo box.
  • ListBox has RowSource set to item selected in combo box. The values in combo box are the names of a bunch of tables. One for each product type all have same column structure.

Today I decided to take a line from the movie "The Princess Bride" and "go back to the beginning".
  • I Created a new workbook dumbed it down with one table and a macro and added the row via macro, no problem.
  • Next, Added a user form with a button tied to macro and code executed again with no issues.
  • Added a list box to the user form and set RowSource to table and bang back to the the big crash when I run the macro.
  • Something is up with ListBox being connected to table. Next, I set RowSource to "" before I invoke"Add" macro and everything works beautiful. I set the RowSource back after the "Add" succeeds and List box is updated with added row.

I don't know if this is a bug in Excel but sure seems like it. I have been programming for 30+ years and I was about to give up thinking Excel is just not stable for this type of program.

Anyway. I would love to hear some opinions on this and hope the info is helpful to others.

Thanks for listening.

John