CODEWITHSUNDEEP
excelvba
Rowin
Rowin

Reputation: 465

Automation error when deleting sheet

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 !

Upvotes: 0

Views: 221

Answers (1)

Marco Vos
Marco Vos

Reputation: 2968

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

Upvotes: 1

Related Questions