cant get code to jump down to my onerror script when encountering this error...

[kbishop94]

when the code is searching for visible cells, and theres none to be found, I get this error (instead of what I would like it to do which is to jump down to my bm_Error_norecords line.) How can I get it to do this? Thanks

Dim rNone As Long
On Error GoTo bm_Error_norecords
    rNone = Worksheets("REPORTS").Range(Cells(20, 2), Cells(rRow, 2)).SpecialCells(xlCellTypeVisible).Cells.Count
On Error GoTo bm_Error_norecords
        If rNone < 1 Then
        MsgBox "   Sorry, ZERO records were found. Please " & vbNewLine & "   perform a new SEARCH but use either a " & vbNewLine & "   new date range or with different variables. "
    With ws
        ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
        ActiveSheet.Range(Cells(18, 1), Cells(rCol, 49)).AutoFilter Field:=2
        ActiveWorkbook.Worksheets("Seatex Incident Log").Range(Cells(rCol, 1), Cells(rCol, 1)).Select
    End With
Unload Me
frmReportCriteria.Show

 

[Candyman8019]

You have added the error handler, but It looks like you're missing the error actions. Before the end of your script, add:

bm_Error_norecords:
'do something because an error has occurred, such as:'
msgbox "There are no more visible cells"

 

[kbishop94]

You have added the error handler, but It looks like you're missing the error actions. Before the end of your script, add:

bm_Error_norecords:
'do something because an error has occurred, such as:'
msgbox "There are no more visible cells"

Thanks Candyman8019.

Here is what it is doing now:

So I do get my MsgBox response when it gets re-directed to my onerror code:


but unfortunately after I hit 'ok' it still goes into the debugger instead of unloading the form.:



Any suggestions?

Thank you!

 

[kbishop94]

apologies, I forgot the revised code:

bm_Error_norecords:
'
MsgBox "Sorry, no INCIDENTS were found for the incident types" & vbNewLine & "selected and between the two dates that were entered." & vbNewLine & " " & vbNewLine & "Please choose a different DATE RANGE or" & vbNewLine & "more/different INCIDENT TYPES and try again."
'
Unload Me

bFlag = True

 

[Candyman8019]

If you want to allow the user to re-enter dates, you'll need to call your form again. I'm assuming it's the frmReportCriteria that you use to enter dates.

bm_Error_norecords:
'
MsgBox "Sorry, no INCIDENTS were found for the incident types" & vbNewLine & "selected and between the two dates that were entered." & vbNewLine & " " & vbNewLine & "Please choose a different DATE RANGE or" & vbNewLine & "more/different INCIDENT TYPES and try again."
'
Unload Me
frmReportCriteria.Show
bFlag = True

 

[kbishop94]

If you want to allow the user to re-enter dates, you'll need to call your form again. I'm assuming it's the frmReportCriteria that you use to enter dates.

bm_Error_norecords:
'
MsgBox "Sorry, no INCIDENTS were found for the incident types" & vbNewLine & "selected and between the two dates that were entered." & vbNewLine & " " & vbNewLine & "Please choose a different DATE RANGE or" & vbNewLine & "more/different INCIDENT TYPES and try again."
'
Unload Me
frmReportCriteria.Show
bFlag = True

Well crap, I cant believe I missed that (you can see that I actually did have it in my first code that i posted in my original question...) I just completely forgot about it when I was revising this section.

So that worked great... (meaning no VB error is now showing), and it instead now correctly calls the form open, so THANK YOU very much for that.

but... sorry, I ran into another snag...

They way that I currently have the code set up, and when the code is successfully ran without issue (meaning that at least 1 record was found that matches the search criteria that was previously selected), it still bumps into my bm_Error_norecords script and it shows the "please try again" message box (even though after you manually exit out of the sub or close the form that reopened, you could see on the 'search results worksheet' where all the found records are presented.) I cant see why or where I have something wrong as it now runs that messagebox regardless if records (rows) are found or not.


here is the very bottom of my 'get record' sub showing the code that shows the bm_Error_norecords code at the end of the sub:

'
bm_Error_norecords:
'
MsgBox "Sorry, no INCIDENTS were found for the incident types" & vbNewLine & "selected and between the two dates that were entered." & vbNewLine & " " & vbNewLine & "Please choose a different DATE RANGE or" & vbNewLine & "more/different INCIDENT TYPES and try again."
'

On Error Resume Next
Unload Me
frmReportCriteria.Show

bFlag = True
'
ActiveWorkbook.Worksheets("REPORTS").Activate
Application.GoTo Reference:=Range("A2"), Scroll:=True
'
' STEP (12):
' THE REMAINING CODE FOR THE SEARCH FEATURE DEALS WITH SORTING, TALLYING & PRESENTING THE NUMBERS THAT REPRESENT INCIDENT TYPES AND THE NUMBER OF FOR EACH ONE.
' THIS IS HANDLED VIA A SEPARATE MODULE WHERE THE CODE RESIDES (MODULE 17; "IncidentTally" )
'**********************************************************************************************************************************************
'
Call IncidentTally
'
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
'
End Sub

 

[Candyman8019]

Move your error condition to the bottom of the sub and add an exit sub command

bm_Error_norecords:
'
MsgBox "Sorry, no INCIDENTS were found for the incident types" & vbNewLine & "selected and between the two dates that were entered." & vbNewLine & " " & vbNewLine & "Please choose a different DATE RANGE or" & vbNewLine & "more/different INCIDENT TYPES and try again."
'
Unload Me
frmReportCriteria.Show
exit sub

 

[kbishop94]

Move your error condition to the bottom of the sub and add an exit sub command

bm_Error_norecords:
'
MsgBox "Sorry, no INCIDENTS were found for the incident types" & vbNewLine & "selected and between the two dates that were entered." & vbNewLine & " " & vbNewLine & "Please choose a different DATE RANGE or" & vbNewLine & "more/different INCIDENT TYPES and try again."
'
Unload Me
frmReportCriteria.Show
exit sub

I'm sorry (again...) (hopefully thats the last time!)

I fixed it. I should of looked more closely at the revised code before hastily posting my reply like I did.

I had my last 'Else End If' in the wrong place. I forgot to move it back down where it belonged below the bm_Error_norecords:

bm_Error_norecords:
'
MsgBox "Sorry, no INCIDENTS were found for the incident types" & vbNewLine & "selected and between the two dates that were entered." & vbNewLine & " " & vbNewLine & "Please choose a different DATE RANGE or" & vbNewLine & "more/different INCIDENT TYPES and try again."
'

On Error Resume Next
Unload Me
frmReportCriteria.Show

bFlag = True
'
ActiveWorkbook.Worksheets("REPORTS").Activate
Application.GoTo Reference:=Range("A2"), Scroll:=True
'
' STEP (12):
' THE REMAINING CODE FOR THE SEARCH FEATURE DEALS WITH SORTING, TALLYING & PRESENTING THE NUMBERS THAT REPRESENT INCIDENT TYPES AND THE NUMBER OF FOR EACH ONE.
' THIS IS HANDLED VIA A SEPARATE MODULE WHERE THE CODE RESIDES (MODULE 17; "IncidentTally" )
'**********************************************************************************************************************************************
'
[B]Else
End If[/B]
'
Call IncidentTally
'
ActiveWorkbook.Worksheets("REPORTS").Activate
Application.GoTo Reference:=Range("A2"), Scroll:=True
'
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
'
End Sub

But, I was wrong when I said that it correctly works now and it re-opens the userform instead of showing me the VB error... so it DOES reopen the form, but if you close out the re-opened form, the same VB error comes up wiht the debugger. (if you dont close the form, and you run another search, but a successful one instead, then its fine... but the way it is right now and if you dont run a successful search and try and close the userform, then you get hit with the VB error. )

 

[Candyman8019]

Your error handler should be at the bottom like this. You didn't have any indicators that the error handler ended...so any code after that just keeps running. Give this a try

bFlag = True
'
ActiveWorkbook.Worksheets("REPORTS").Activate
Application.GoTo Reference:=Range("A2"), Scroll:=True
'
' STEP (12):
' THE REMAINING CODE FOR THE SEARCH FEATURE DEALS WITH SORTING, TALLYING & PRESENTING THE NUMBERS THAT REPRESENT INCIDENT TYPES AND THE NUMBER OF FOR EACH ONE.
' THIS IS HANDLED VIA A SEPARATE MODULE WHERE THE CODE RESIDES (MODULE 17; "IncidentTally" )
'**********************************************************************************************************************************************
'
[B]Else
End If[/B]
'
Call IncidentTally
'
ActiveWorkbook.Worksheets("REPORTS").Activate
Application.GoTo Reference:=Range("A2"), Scroll:=True
'
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

bm_Error_norecords:
MsgBox "Sorry, no INCIDENTS were found for the incident types" & vbNewLine & "selected and between the two dates that were entered." & vbNewLine & " " & vbNewLine & "Please choose a different DATE RANGE or" & vbNewLine & "more/different INCIDENT TYPES and try again."

Unload Me
frmReportCriteria.Show
Exit sub
End Sub