Menu
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.
997
Basic Error Handling Overview To shut off (disable) the active handler, use On Error GoTo 0 . Doing so will close off the code block that uses that handler. Alternatively, exit the subroutine using Exit Sub , which automatically turns off the handler.
On Error GoTo 0 disables error handling in the current procedure. It doesn't specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0. Without an On Error GoTo 0 statement, an error handler is automatically disabled when a procedure is exited.
The “On Error Resume Next” is the error handler statement when we need to ignore the known error. If we want to ignore the error message only for a specific code set, close the On Error Resume Next statement by adding the “On Error GoTo 0” statement.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
