how to autofill Textboxes based on Dropdown box selection

[Scottv7]

I'm not quite sure how to word this but here is what I'm trying to do.

I have a user form that contains a dropdown box to select specific vendors. I would like the corresponding data to populate from that row to into the other text boxes within the form.

the issue that I have is that I'm not sure how to get the form to select the specific cell (based on the drop down selection) so that I can use the offset coding to grab the other data.


I have a sheet is called "Vendors" and I created a range ("chk_vendors") that is referencing A2:A1000 that will populate the dropdown box.
I also created a range called ("chk_AccountDatabase") that is referencing A2:I1000 in case it is needed.

here is the initialize code that I have to populate the drop down box (called dd_payment) in case that helps.

Private Sub UserForm_Initialize()

'Vendor Drop Down
Set VendorRange = Range("chk_Vendors")
For Each VendorCell In VendorRange
If VendorCell.Value <> vbNullString Then
Me.dd_Payment.AddItem VendorCell.Value
End If
Next VendorCell

End Sub

I'm stuck trying to get the other data in that vendor's row (ie. address, city, state, zip ect.) into their respective text boxes within the same form.

Thanks in advance for your help.

 

[Norie]

Is dd_Payment the dropdown that will list the vendors?

 

[Scottv7]

yes, it populates the vendors.

 

[Norie]

Try this and check out this example workbook.

Private Sub dd_Payment_Change
Dim Res As Variant

    If dd_Payment.ListIndex <> -1 Then

        Res = Application.Match(dd_Payment.Value, Range("chk_vendors"), 0)

        If Not IsError(Res) Then
            ' fill textboxes
            With Range("chk_AccountDatabase")
                TextBox1.Value = .Cells(Res, 2).Value
                TextBox2.Value = .Cells(Res, 3).Value
                ' etc
             End With
        End If
    Else
        MsgBox "No vendor selected!"
    End If

End Sub

 

[Scottv7]

That worked like a charm. thanks so much!!