I had an Excel workbook with a listbox ActiveX control on one of the sheets and some VBA coding. The Excel workbook was originally created in Excel 2007 and worked fine for years but when we upgraded to Excel 2010 we started getting the "Could not set the RowSource property. Unspecified error" message after a very short period of use. It would work one or two run but then suddenly stops.
What I found the reason for this error to be in my case is that the Listbox events were firing every time the worksheets were updated by my VBA script. My VBA script updated a lot of cells each time it ran and the Change, Select and other Listbox events were going off like crazy. I think this overwhelmed Excel 2010 and it gave up after an X amount of times.
The solution for this problem was to set the range that I was using to populate the Listbox to an Array then set the Array to the Listbox from VBA. My named range is called dept and is on the sheet called dump so this is what I did:
myArray = ThisWorkbook.Sheets("dump").Range("dept").Value
lstDept.List = myArray
Not only my script worked on Excel 2010 but it ran faster on Excel 2007 due to the events no longer firing.
