Instant error with application.inputbox cancel!!

[Rudi]

Hi,

I am getting an error when I cancel the application.inputbox. I thought I had the right checks in place to handle the cancel of this object, but it debugs immediately even though I have on error resume next... What am I doing wrong??? TIA

Code: Select all

EH:
    MsgBox "The search statement... " & vbNewLine & vbNewLine & _
        "'The following portfolios were included in this compliance procedure'" & vbNewLine & vbNewLine & _
        " ...could not be found in this report. This statement helps to determine the location of" & _
        " the portfolio lists that need to be sorted. " & vbNewLine & vbNewLine & "Please supply " & _
        "the location manually...", vbExclamation
    On Error Resume Next
    'if user cancels input it produces error 424: Object required????
    Set PortfolioListLocation = Application.InputBox("Please select the first portfolio code.", _
        "Portfolio List Location", Range("A6").Address, Type:=8)
    On Error GoTo 0
    If PortfolioListLocation Is Nothing Then Exit Sub
    If PortfolioListLocation.Offset(-1, 0) <> "" Then PortfolioListLocation.EntireRow.Insert
    Set PortfolioListLocation = PortfolioListLocation.CurrentRegion.Cells(1).Offset(-2, 0)

[HansV]

In the Visual Basic Editor, select Tools | Options...
Activate the General tab.
In the Error Trapping section, make sure that "Break on All Errors" is NOT selected. You should either have "Break on Unhandled Errors", or (preferably) "Break in Class Module".

[Rudi]

Hi Hans,

TX for the reply. It is already on Unhanded Errors?

Any other ideas?

[HansV]

In that case, I'd have to see (a copy of) the workbook. Your code works without problems for me.

[Rudi]

TX....Here is the file.

[HansV]

You can't use On Error Resume Next in an error handling section. To work around this, use code like this:

Code: Select all

Private Sub FindPortfolioList()
    On Error GoTo EH
    Set PortfolioListLocation = Cells.Find(What:= _
        "The following portfolios were included in this compliance procedure", After _
        :=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows _
        , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If PortfolioListLocation.Offset(1, 0) <> "" Then PortfolioListLocation.Offset(1, 0).EntireRow.Insert
    Exit Sub
ExitHandler:
    On Error Resume Next
    Set PortfolioListLocation = Application.InputBox("Please select the first portfolio code.", _
        "Portfolio List Location", Range("A6").Address, Type:=8)
    On Error GoTo 0
    If PortfolioListLocation Is Nothing Then Exit Sub
    If PortfolioListLocation.Offset(-1, 0) <> "" Then PortfolioListLocation.EntireRow.Insert
    Set PortfolioListLocation = PortfolioListLocation.CurrentRegion.Cells(1).Offset(-2, 0)
    Exit Sub
EH:
    MsgBox "The search statement... " & vbNewLine & vbNewLine & _
        "'The following portfolios were included in this compliance procedure'" & vbNewLine & vbNewLine & _
        " ...could not be found in this report. This statement helps to determine the location of" & _
        " the portfolio lists that need to be sorted. " & vbNewLine & vbNewLine & "Please supply " & _
        "the location manually...", vbExclamation
    Resume ExitHandler
End Sub

[Rudi]

Thanks, its working on my side too. Your advice is very interesting!?

What determines the error handling section then, as you are also using a label to branch off code? Maybe the only way to "exit" a error handler is to "Resume" code? This can be a little confusing then??

BTW: I sort of picked up on this conundrum (running an error in an error) earlier and I tried to fix it by the statement on error goto 0. In other words:

EH:
on error goto 0
blah blah blah...
on error resume next
....

i thought that the on error goto 0 would switch off the error handling so it was not aware it was in a "error handler".

obviously, it did not work...

Thanks a stack for your help. I really appreciate it! Cheers

[HansV]

The line On Error GoTo 0 does not help because the error has already occurred by then. The line On Error Resume Next has no effect while you are handling an error, that's why

Set PortfolioListLocation = Application.InputBox(...)

displays an error message if the input box is canceled. But the line Resume ExitHandler ends the error handling, so you can then use On Error Resume Next again.

[Rudi]

TX.

I've written a lot of error handling code down the line, but it's the first time I am really concious of the fact that a resume statement ends an error handler. It makes sense now...but what a learning curve to discover this??!!