VLookup? to populate userform text box

[BarefootPaul]

I am trying to populate a text box on a userform using vlookup (not sure if this is the best way). I use a combobox on another userform to populate the txtMaineCare.Value (txtMaineCare.Value = cboLookup.Value) and then want the values for txtFName, txtLName and txtDOB to be populated by the values that correspond. Is there a way to set these other values by using something like cboLookup.Value.Column2 or can only the bound column's value be returned?

Here is the code:

Private Sub txtMaineCare_Change()
Dim vLook As Range
With ActiveWorkbook.Sheets("Clients")
    Set vLook = .Range("A:D")
    txtFName.Value = Application.VLookup(txtMaineCare.Value, vLook, 2, False)
    txtLName.Value = Application.VLookup(txtMaineCare.Value, vLook, 3, False)
    txtDOB.Value = Application.VLookup(txtMaineCare.Value, vLook, 4, False)
    txtDOA.SetFocus
End With
End Sub

When I run it, I get an error:
Run-time error '-2147352571 (80020005)': Could not set the Value Property. Type mismatch.

Thanks

 

[bensonsearch]

try setting a variable to equal txtMaineCare.Value and also make sure the code is not erroring.

also i would use variables for the finding values then setting the txtbox values = the variables eg


Dim vLook As Range
dim fnam as string
dim lname as string
dim DOB as string
dim maine as string

maine = txtMaineCare.Value

With ActiveWorkbook.Sheets("Clients")
Set vLook = .Range("A:D")

fname = Application.VLookup(maine, vLook, 2, False)

if iserror(fname) then

msgbox ("error is with fname")
exit sub
else

lname = Application.VLookup(maine, vLook, 3, False)

if iserror(lname) then

msgbox("erroris with lname")
exit sub
else

DOB = Application.VLookup(maine, vLook, 4, False)

if iserror(DOB) then

msgbox("erroris with DOB")
exit sub


end if
end if
end if

txtDOA.SetFocus
End With

 

[mikerickson]

Instead of using VLookup, you can load the data into the combobox at the time it is loaded. This will fill a four column combobox with data and hide the last three columns. This should go in the first userform, UserForm1, the one the combobox.

Public Sub FillCboLookup()
    Dim oneRow As Range
    
    With cboLookup
        .ColumnCount = 1
        .ColumnWidths = ";0;0;0"
    End With
    
    With Sheet1.Range("A:D")
        With Range(.Cells(1, 4), .Cells(.Rows.Count, 1).End(xlUp))
            cboLookup.List = .Value
        End With
    End With
End Sub

Then the textboxes, in UserForm2, can be filled with

Private Sub CommandButton1_Click()
    With UserForm1.cboLookup
        If -1 < .ListIndex Then
            Me.txtFName.Value = .List(.ListIndex, 1) & "list"
            Me.txtLName.Value = .List(.ListIndex, 2)
            Me.txtDOB.Value = .List(.ListIndex, 2)
        Else
            txtFName.Value = vbNullString
            txtLName.Value = vbNullString
            txtDOB.Value = vbNullString
        End If
    End With
End Sub

If you need a VLookup approach, fill the combobox as you wish and this will fill the textboxes.

Private Sub CommandButton3_Click()
    Dim dataRange As Range
    
    With Sheet1.Range("A:D")
        Set dataRange = Range(.Cells(1, 4), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    With UserForm1.cboLookup
        If -1 < .ListIndex Then
            txtFName.Value = Application.VLookup(.Text, dataRange, 2, False)
            txtLName.Value = Application.VLookup(.Text, dataRange, 3, False)
            txtDOB.Value = Application.VLookup(.Text, dataRange, 4, False)
        Else
            txtFName.Value = vbNullString
            txtLName.Value = vbNullString
            txtDOB.Value = vbNullString
        End If
    End With
End Sub

 

[BarefootPaul]

Thanks to both of you. Here is what I did that meets my needs:

Private Sub txtMaineCare_Change()
Dim vLook As Range
Dim vFName As String
Dim vLNname As String
Dim vDOB As String
Dim vMaine As String
vMaine = txtMaineCare.Value
If vMaine = vbNullString Then 'I had to add this as I was getting an error when the form was reset.
    Exit Sub
    Else
    With ActiveWorkbook.Sheets("Clients")
        Set vLook = .Range("A:D")
        vFName = Application.VLookup(vMaine, vLook, 2, False)
            If IsError(vFName) Then
                MsgBox ("Error is with vFName")
            Exit Sub
            Else
        vLName = Application.VLookup(vMaine, vLook, 3, False)
            If IsError(vLName) Then
                MsgBox ("Error is with vLName")
            Exit Sub
            Else
        vDOB = Application.VLookup(vMaine, vLook, 4, False)
            If IsError(vDOB) Then
                MsgBox ("Error is with vDOB")
            Exit Sub
            End If
            End If
            End If
    End With
End If
txtFName.Value = vFName
txtLName.Value = vLName
txtDOB.Value = vDOB
txtDOA.SetFocus
End Sub

mikerikson, I wasn't really sure how to follow all of that as I am fairly new to this. I am using a named range as the rowsource for the combobox and that seems to work nicely. Is there a reason to populate the combobox this way instead of using a rowsource?

Thanks again, this is much better than the work around I had come up with.

 

[mikerickson]

The only reason would be if you wanted a multi-column list box to store the data and keep it all on the UF.