Exiting Excel fires Change Event on ListBox

[24601]

Hi all,

I'm struggling with an issue and hoping someone may be able to help.

I've created a Change Event for a listbox which is working well. The purpose of this is to record the selected items from the multi select listbox and store them on the spreadsheet & clear the previously stored list.

The problem is when I try to close the application using the top right 'X'. I get a Run time error, ClearContents method of Range class failed. When I debug this I can see that the Change Event for the List box is the cause, in particular, the line which clears the range.

2 questions....

1, Why is this Change Event firing when exiting the application? It doesn't seem to if I exit the workbook via File-> Close. Only seems to be if I use the 'X' in the top right.

2, Why is the Change Event not working when exiting the application, but works fine when you change something in the list box? Has it to do with the fact the Change Event is recorded on Sheet2 object?

Please Login or Register  to view this content.

hoping this is simple, as I'm a newbie to VBA.

Thanks.

[Norie]

How are you populating the listbox?

[24601]

Hi Norie,

I'm using a Combo box to determine which Range is used to populate the ListFillRange on the Listbox.

[Norie]

Are the ranges you use for ListFillRanges dynamic named ranges?

If thet are they might be being recalculated when you exit the workbook, and that could be what's triggering the change event.

[24601]

Yes, they are dynamic named ranges. So if we assume that's what's causing the change event to trigger, any idea why it is failing (only when exiting application)?

[Norie]

Not sure why you get the error when trying to clear contents.

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[24601]

File attached.

[Norie]

This is weird, if I open the workbook and the close it again there's no error, if I open the workbook, open the VBE, close the VBE and then close the workbook I get the error.

In the attached I've changed things a bit from the original, mainly using List to populate the listboxes instead of ListFillRange and now I don't seem to get the error.

Can you give it a try?

By the way, and this maybe a stupid question, but why are you duplicating Sheet2 in a userform (or is it the other way round)?

[24601]

I was originally going to complete this using a userform but changed my mind half way through!

This is very strange, I've noticed that if I have another random workbook open at the same time, I don't get the error, but if it's the only workbook open then the error appears. The error seems to trigger when the entire application is closed at the same time as the workbook.

Using your revised workbook, I seem to be getting an error when changing the combo box value...Method 'Range of object'_Worksheet' failed...looks to be linked to the using of the .Link to populate the listboxes.

[Norie]

That sounds more like a problem with a named range, have you checked them out?

By the way, what's .Link?

[24601]

Sorry, that was a typo. meant to be .List not .Link.

I'll check the named ranges to see if there is any issues there.

Thanks for your assistance with this.