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

 

[gjp55]

Im still getting the same error after adding your code suggestion.

It was definately an activex label.

With regards to the form controls listboxes, all I need them to do is display a range of values (in another worksheet thats hidden), be multiselect and hide when not active. Several listboxes will be in the same position but only the active one will be visible. As a value is selected, another list box will display the value selected or remove if deselected.

I have some functionality with the labels also where by they change color to indicate if a value has been selected.

I also have a few default selections on listboxes.

All of the above is done through VBA. If this can be done in macro's them I will use them instead if it solves the problem. Obviously my preference is with resolving the activex issues though.
thanks

 

[RoryA]

Macros are VBA so I'm not sure what you mean.

Without seeing the workbook I don't think I can really troubleshoot it I'm afraid.

 

[gjp55]

Sorry, Im not being very clear. Would it be possible for you to look at the workbook ?

 

[RoryA]

I will PM you an email address. Can't guarantee when I will be able to look at it, but will do so as soon as I can.

 

[gjp55]

Fantastic, thanks very much

 

[gjp55]

It looks like the problem might be becuase on some of the list boxes have 2 columns (the hidden column is the bound value). If I change all of the list boxes to be only one column, the problem goes away.

Is this something to do with the index = -1 setting ?

 

[RoryA]

I've never heard of that being a problem and I don't know what you mean by your last comment.

 

[gjp55]

Sorry, I meant ListIndex = -1

http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.programming/2008-09/msg00075.html

 

[RoryA]

As I mentioned before, without the workbook I can't really assist.

 

[gjp55]

Is it possible to see from the workbook I sent you ?