Automation error when deleting sheet

Question

I'm working on a little sub to delete specific sheets, according to a form display to the user. The user choose which sheet he wants to keep in a listbox. The code then loop through all the sheets in the listbox and delete the sheets that are not selected. But, when running the form and click on the button I got an automation error :

Execution error '-2147417848 (80010108): Automation Error object invoked has disconnected

Private Sub Button_Export_Click()
    Application.DisplayAlerts = False
    For i = 0 To PSV_Case_List.listCount - 1
        If Not PSV_Case_List.Selected(i) Then
            Worksheets(PSV_Case_List.List(i)).Delete

        End If
    Next i
    Application.DisplayAlerts = True
End Sub

Following this message, Excel freezes and I have to kill the process.

Any idea ?

Thanks !

Marco Vos Marco Vos · Accepted Answer · 2017-08-25T14:35:41

When you do deletes like this, you should allways start with the highest index and work your way down, because as soon as sheet(0) is deleted, sheet(1) becomes sheet(0) and so on. So try this:

For i = PSV_Case_List.listCount - 1 to 0 step -1

Automation error when deleting sheet

1 Answers