ListRows.Add crashes Excel and restarts

Hi,

This is about excel crashing with .ListRows.Add line for a table. I have searched various forums for this issue. But haven't received a solution. In case anyone has a solution please help.

The problem is the code works at times and crashes at some other times. Sometimes, I add one record and it works. And when I add the second record it crashes.

I am using Office 365.

There are two excel files.

File 1: This is the macro enabled file with user forms
File 2: This has four sheets: Customer Master Data, Services Master Data, Service Profile & Service Log.

The problem is with the table in the Services Master Sheet of File 2.

The relevant code having the problem is as follows. I have marked the code line where excel crashes as "EXCEL CRASHES AT THIS LINE"

Please Login or Register  to view this content.

Do you populate the table "ServMastTbl" data to a Listbox or Combobox in the userform? and use Rowsource property to do that?

Yes. I populate the ServMastTbl data to a Listbox and use the Rowsourse property. Am I not supposed to do that?

Well, Rowsorce isn't reliable, please read this article:
http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

"If you use 'rowsource' you make a direct link to a certain range in a worksheet.
Combined with the combobox it can cause Excel to blackout, because any change in the combobox will be transferred directly to the 'source'.
Excel has proven not to be able to do this correctly.
Besides: the changing of the source at every change in the combobox slows down your code.
You should reduce the reading/writing from/to a workbook in your code as much as possible."

Thank you for your help. I will now figure out how to use Array to populate the listboxes in my application.

rgds
hemant

You're welcome, glad to help, & thanks for the feedback.

Hi Akuini,

A question: Will it be all right to use a 2-D array to store thousands of rows of a table ?

e.g. a table having 10-20 thousand rows and 30-40 columns

Is there a limit or recommended size in using 2-D arrays to store table values? Or any dependencies like Excel version or Computer Memory?

rgds
hemant

Another question. Please refer to the beginning of my code above.

Is it okay to have so many Public variables is not an issue? These are used in other 3-4 forms too.

Sorry, your question is beyond my knowledge and because this is different from your initial problem, you may need to start a new thread.

No Problem. Thanks for your help. I have used Array to populate list box and now Excel does not crash.

Ok, glad to hear it works.