vba error handling in loop

Question

New to vba, trying an 'on error goto' but, I keep getting errors 'index out of range'.

I just want to make a combo box that is populated by the names of worksheets which contain a querytable.

    For Each oSheet In ActiveWorkbook.Sheets
        On Error GoTo NextSheet:
         Set qry = oSheet.ListObjects(1).QueryTable
         oCmbBox.AddItem oSheet.Name

NextSheet:
    Next oSheet

I'm not sure whether the problem is related to nesting the On Error GoTo inside a loop, or how to avoid using the loop.

Answer 1

The problem is probably that you haven't resumed from the first error. You can't throw an error from within an error handler. You should add in a resume statement, something like the following, so VBA no longer thinks you are inside the error handler:

For Each oSheet In ActiveWorkbook.Sheets
    On Error GoTo NextSheet:
     Set qry = oSheet.ListObjects(1).QueryTable
     oCmbBox.AddItem oSheet.Name
NextSheet:
    Resume NextSheet2
NextSheet2:
Next oSheet