VLookup In UserForm

Hi Guys,

I am undertaking my first Userform set-up after following several templates/guides available on the web, however I think I have jumped in the deep-end by creating such a big project.

I have completed the form design, named all list/boxes etc and have done the majority of coding (at least I think so far). I am know having trouble getting some sort of VLookup to work within the Userform having tried various different codes.

I have attached a slimmed down version of my project and would like the following

cboReg - This it linked to a named range (column A4 down)
txtMake - I would like this to be pulled through from cboReg selection (Make is in column B)

If someone could get me started I will be able to transpose to the rest.

If you spot any inherent errors please point them out, also I am planning on hiding all the sheets so users can only use this UserForm (I have some code for this already) if this has any bearing on any codes.

Thanks in advance

Stuart

Hi

Paste the following code into your form and it will populate the relevant fields when you select an item from the drop down list.

Private Sub cboReg_Change()

    Dim vreg As String, drow As Integer, c As Range

    Let vreg = cboReg.Value

    If cboReg.Value = "" Then
        MsgBox "Please select a value from the drop down list"
        Exit Sub
    End If

    With Sheets("Vehicle List To Update").Range("a4:a100")
        Set c = .Find(vreg, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            Let drow = c.Row
        End If
    End With
    
    Let txtMake.Value = Sheets("Vehicle List To Update").Cells(drow, 2).Value
    Let txtModel.Value = Sheets("Vehicle List To Update").Cells(drow, 3).Value
    Let txtCC.Value = Sheets("Vehicle List To Update").Cells(drow, 4).Value
    Let txtGVW.Value = Sheets("Vehicle List To Update").Cells(drow, 5).Value

End Sub

You can extend it to add as many as required.

If you need anything else please let me know.

Regards

Jeff

Cheers Jeff,

I have added the additional coding to the rest of my requirements, however when I now try to use the cboReg for data input I get Run-Time Error '1004' an when I go into Debug it highlights this line:

Let txtMake.Value = Sheets("Vehicle List To Update").Cells(drow, 2).Value

This is the full code of what I have now:

Private Sub cboReg_Change()

    Dim vreg As String, drow As Integer, c As Range

    Let vreg = cboReg.Value

    With Sheets("Vehicle List To Update").Range("a4:a100")
        Set c = .Find(vreg, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            Let drow = c.Row
        End If
    End With
    
    Let txtMake.Value = Sheets("Vehicle List To Update").Cells(drow, 2).Value
    Let txtModel.Value = Sheets("Vehicle List To Update").Cells(drow, 3).Value
    Let txtCC.Value = Sheets("Vehicle List To Update").Cells(drow, 4).Value
    Let txtGVW.Value = Sheets("Vehicle List To Update").Cells(drow, 5).Value
    Let cboVehicleType.Value = Sheets("Vehicle List To Update").Cells(drow, 6).Value
    Let cboCover.Value = Sheets("Vehicle List To Update").Cells(drow, 7).Value
    Let txtAdded.Value = Sheets("Vehicle List To Update").Cells(drow, 9).Text
    Let txtDeleted.Value = Sheets("Vehicle List To Update").Cells(drow, 10).Text
    Let txtDept.Value = Sheets("Vehicle List To Update").Cells(drow, 15).Value
    Let txtLocation.Value = Sheets("Vehicle List To Update").Cells(drow, 16).Value
    Let cboDriver.Value = Sheets("Vehicle List To Update").Cells(drow, 14).Value
    Let txtCondition.Value = Sheets("Vehicle List To Update").Cells(drow, 15).Value
    Let txtMileage.Value = Sheets("Vehicle List To Update").Cells(drow, 18).Value
    Let txtRadio.Value = Sheets("Vehicle List To Update").Cells(drow, 20).Value
    Let txtKeyNo.Value = Sheets("Vehicle List To Update").Cells(drow, 19).Value
    Let txtTAX.Value = Sheets("Vehicle List To Update").Cells(drow, 12).Text
    Let txtMOT.Value = Sheets("Vehicle List To Update").Cells(drow, 13).Text
End Sub

Thanks

Stuart

Hi

It sounds as though you do not have a value for drow, which would cause the error. When debugging place the cursor over the word drow and a popup will show the value, if it is zero then there was no match for the reg no., you can avoid this using an if statement to trap the error with a message box explaining why and then an exit sub statement to take you back to the form e.g.

With Sheets("Vehicle List To Update").Range("a4:a100")
        Set c = .Find(vreg, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            Let drow = c.Row
        Else
        Msgbox "This Reg. Number does not exist, please retry.
        Exit Sub
        End If
    End With

If not then it would be useful to see a copy of the sheet to find out what is causing the error.

regards


Jeff