User Form Listbox.Rowsource Problem and Solution

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

File is attached - Form is displayed by Ctrl-Shift+M

I Might add I am using Excel 2016 Pro (Office 365) and Windows 10 Enterprise

Look at Code for ProdManager form in the Add button.

Thanks
John

I think your version is cleaner/better and will try to use these techniques - you are doing a few things I will try to understand.

Thanks for taking the time to provide this version.

I had this Excel crash problem as well. It seems the crash was caused by having the Listbox.RowSource set to the table or range while the code was doing the update work to that table or range.

The crash stopped when I set the Listbox.RowSource to "", updated the data, and then reconnected the Listbox.RowSource to the range. I am actually setting the RowSource = to a ListObject.

Edward, thank you so much!
I was having this problem and I'm not too familiar with VBA and was desperate haha. I used your solution in the "add" button code and works perfectly. It doesn't crash anymore.

Originally Posted by EdwardMartin

I had this Excel crash problem as well. It seems the crash was caused by having the Listbox.RowSource set to the table or range while the code was doing the update work to that table or range.

The crash stopped when I set the Listbox.RowSource to "", updated the data, and then reconnected the Listbox.RowSource to the range. I am actually setting the RowSource = to a ListObject.

Hello, I am sorry for necroing the thread - how exactly does one do that? I am having the exact same issue, using listbox Rowsource and having runtime error.

Private Sub AddDataToListBox()
'grab the range
    Dim rg As Range
    Set rg = GetRange()
    
'link the range to the list box
    With ListBoxArtikel
    .RowSource = rg.Address(external:=True)
    .ColumnCount = rg.Columns.Count
    .ColumnWidths = "30;100;80;310;0;0;70;70;70;70;70;70;70;70;70;0;0;0;0;0;0;0"
    .ColumnHeads = True
    .ListIndex = 0
    End With
    
End Sub

So I should have "" instead of "rg.Address(external:=True)", and then somehow update it afterwards? How?

Thank you for any help.