Nsryjdtyk

Amateur coder asking for help, again :-)

I have the below code that works good, it gets a variable from a drop down validation list and executes command automatically because I and using the Private Sub Worksheet_Change(ByVal Target As Range). The issue I have is that sometimes the user types into the box rather then select it.

I get a

run-time error 91

because it cannot find the value typed in by user.

How can I catch this error and make a message popup to tell them they must choose from drop down list? And Also since it breaks the code it sets my Application.EnableEvents to False since it doesn't finish the code from the ByVal Target As Range section that resets to True.

How can I reset the Application.EnableEvents to True in the event of a code break?

Sub Copy_From_Borrower_DBase()

    Dim myVal As String

    Dim sourceRng As Range

    myVal = Sheets("Main").Range("F2").Value ' dropdown list

    Set sourceRng = Worksheets("Borrower Database").Range("5:5").Find(What:=myVal, LookAt:=xlWhole) 'locate column where to copy from

    Worksheets("Main").Range("F5").Value = Worksheets("Borrower Database").Cells(5, sourceRng.Column).Value 'Borrower Name

    Worksheets("Main").Range("G6").Value = Worksheets("Borrower Database").Cells(6, sourceRng.Column).Value 'Income

End Sub

Check first that the range does not return Nothing (meaning the term was not found). The use of With is basically cosmetic, but I think tidies up the code somewhat.

Sub Copy_From_Borrower_DBase()



Dim myVal As String

Dim sourceRng As Range

myVal = Sheets("Main").Range("F2").Value ' dropdown list



With Worksheets("Borrower Database")

    Set sourceRng = .Range("5:5").Find(What:=myVal, LookAt:=xlWhole)

    If Not sourceRng Is Nothing Then

        Worksheets("Main").Range("F5").Value = .Cells(5, sourceRng.Column).Value 'Borrower Name"

        Worksheets("Main").Range("G6").Value = .Cells(6, sourceRng.Column).Value 'Income"

    Else

        MsgBox "whatever"

    End If

End With



End Sub

To answer the question about error handling:

Public Sub MyProcedure()

    Application.EnableEvents = False



    On Error Goto ENABLE_EVENTS 'if any error occurs after this line events get enabled.



    'stuff where an error could occur



ENABLE_EVENTS:

    Application.EnableEvents = True

    If Err.Number <> 0 Then

        MsgBox "an error occurred"

    End If

End Sub

For more information about error handling: VBA Error Handling – A Complete Guide.

I would first try to solve the problem by not allowing there to be a problem, if that fails then worry about error handling.

Since you are already calling Worksheet_Change and using Data Validation, this checks the value of the validation cell. If it has a value in it then your code will run. If it is empty it will not. Data Validation clears the cell if someone types in it, so no matter what they type by the time it hits Worksheet_Change it should be an empty string.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$F$2" Then         ' Your Dropdown Validation Cell

        If Not Target.Value = "" Then

            Call Copy_From_Borrower_DBase

        End If

    End If

End Sub