Active X Objects in worksheet problems

[gjp55]

Hi,

I have problem regarding Active X boxes in a worksheet. I will be working on the code and click the save button which works fine. The problem comes when I close the workbook (even if I select no to saving changes). I get object errors on various listboxes where it can not fine the listbox object. I have the excel spreadsheet saved a various stages of development where the early versions save on but as soon as I go into the code , this can set the errors off when I come to close the file.

I have been reading a few posts about problems with Excel worksheets and active x objects and how they dont work well together.

I have tried somebody else running the file on another pc and they still get the error.

Does anybody have any idea have any idea how to solve this ?
I can provide a sample of the file it this helps ?

Thanks

 

[RoryA]

It's only just arrived in my Inbox and I haven't had a chance to look at it yet, I'm afraid.

 

[gjp55]

Im sure that is the problem now. As soon as I add an additional column to the listbox, all of the compile errors come back.

Is there a correct method to configure a list box where there is more than one column ? ( ie. a description and a numerical ID). I want to store the id rather than the description becuase this is ultimatley being passed into a stored procedure .

ListFillRange spans two cell columns (ie. A1:B10)
Col A = 1,2,3,4,5,6,7,8,
Col B = Descr1, Descr2, descr3, etc

Im setting BoundColumn = 1
ColumnCount = 2
ColumnWidths = 0pt; 20pt ' to make the description column visible and the ID hidden

Is there anything else that should be set on the object or when referring to it in VB ?

 

[RoryA]

That should be all you need.

 

[gjp55]

Thanks very much for taking the time to look at this for me, I really appreciate it.

I will use just the description columns for now and look up the id's at a later stage in the process. Its strange way it wont work and if I do ever find out why, I will post back.

Thanks

 

[jkpieterse]

Just for my information: what Excel version are you using?

 

[gjp55]

I'm using 2010 but saving as 97-2003
What method would recommend for looking up a cell value ?

 

[RoryA]

Based on the sample file you sent, what steps do you take to cause the error? (I'm not getting any errors)

@JKP,
Seems like more ActiveX/2010 issues?

 

[jkpieterse]

@JKP,
Seems like more ActiveX/2010 issues?

Yes indeed. So, do you ping MSFT on this or do I?

 

[RoryA]

I did reproduce the errors just as I shut down my laptop last night, so I'll try and put together a simple workbook to demo the issue and file it on Connect. Then I'll add a note to your MS ESC posting to draw more attention to the issue?

 

[jkpieterse]

Yes please!

NB: I posted a link to this thread in that MS ESC thread already.