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
Bookmarks