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?

Private Sub lstAddMods_Change()
    Dim I As Long
    
    Columns(21).ClearContents

    With Me.lstAddMods
        For I = 0 To .ListCount - 1
        If .Selected(I) Then Worksheets("Sheet2").Range("U65").End(xlUp).Offset(1, 0).Value = .List(I)
        Next
    End With
  
End Sub
hoping this is simple, as I'm a newbie to VBA.

Thanks.