Bypass the run time error if user enters a wrong value

**Richard Flame** · 05-30-2007, 11:01 AM

Hello everyone,

I have multiple pivot tables that contain information about distributors. The user can enter the distributor id in one cell and click a button that will set the distributor ID fields in the pivot tables to the ID entered by the user. Now when or if the user enters a wrong Distributor ID and click the button the run time error message pops up(for sure it wwill since it did not find the id in the pivot tables). I know I can create a list (data validation) where the user can select a specific ID but this is slowing my application a lot (trying to minimize the file size as much as possible cuz there are more than 10000 Ids). How can I fix this problem? In other word if the user enters a wrong Id I don't the error message to op up instead something else like a message to reenter a valid ID.

Thank you

**boylejob** · 05-30-2007, 11:23 AM

Richard,

I am not exactly sure how you have this set up, but two things come to mind. You could use an error handler like the one below. The On Error GoTo can go pretty much anywhere within your subroutine and the Exit Sub stuff goes right before the End Sub statement (at least that is how I have it set up).

On Error GoTo ErrorHandler

Exit Sub        ' Exit to avoid handler.
ErrorHandler:    ' Error-handling routine.

    'Your code goes here
    Resume 40 'Tell where the program should start up again

or

I have to search on an ID and I handle it this way

    Set rEFC = wsEFC.Cells.Find(what:=ssn, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
    
    If Not rEFC Is Nothing Then
       'If ssn is found do the code placed here
    Else
       'If ssn is not found do the code placed here
    End If

Using the two examples above, you could code for your msgbox or whatever in the error handler or the else part of the if statement.

Maybe this will give you some ideas