[RESOLVED] Form error in excel

[thelocaluk]

Hi, I am a total newbie and I'm playing around with a little form where you enter a number then it finds and displays the appropriate info. It works ok apart from when you put the wrong number in, then it comes up with an error.
You'll have to have a look at it to understand what I mean.

[Shuja Ali]

Hi, I am a total newbie and I'm playing around with a little form where you enter a number then it finds and displays the appropriate info. It works ok apart from when you put the wrong number in, then it comes up with an error.
You'll have to have a look at it to understand what I mean.

You code in the Find Button should test the rCell object like this

Set rCell = Cells.Find(What:=TextBox1, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        )
    If rCell Is Nothing Then
        bFound = False
        MsgBox "Sorry, no matches", vbOKOnly
    Else
        bFound = True
        ListBox1.AddItem rCell(1, 2).Cells
        ListBox2.AddItem rCell(1, 3).Cells
    End If

And why are you disabling the Error handler? It is always a good idea to have the Error Handler code in your procedure.

[DKenny]

If you read the help file for the Find Method, it says "Returns Nothing if no match is found". You can use this to trap for those situations where the user enters an invalid Job Id.
To do this, you should change your IF statement from

If TextBox1 Like rCell Then
        bFound = True
        ListBox1.AddItem rCell(1, 2).Cells
        ListBox2.AddItem rCell(1, 3).Cells
    Else
        bFound = False
        MsgBox "Sorry, no matches", vbOKOnly
    End If

to

If rCell Is Nothing Then
        bFound = False
        MsgBox "Sorry, no matches", vbOKOnly
    Else
        bFound = True
        ListBox1.AddItem rCell(1, 2).Cells
        ListBox2.AddItem rCell(1, 3).Cells
    End If

[thelocaluk]

Thanks guys, it all makes perfect sense when you see it.