Populate text fields with value from a listbox

[goldenvision]

I have spent a couple of hours searching for a solution to this problem and either I am not asking the question correctly or it cannot be done.

This is my problem. On a user form I have a combo box which is populate with vehicle marques. Each of these marques relates to a namd range on a spreadsheet. Selecting a marque from the combo box populates a listbox with records from that range.

What I want to be able to do is click a record (which has a unique ref number) and search the master list then populate a series of text boxes with the selected record.

Each record has around 40 columns of data, therefore the named ranges only contain an abridged version of the data.

Is what I am asking possible?

Any pointers would be greatly appreciated as I don't have a huge amount of hair left to pull out!!

 

[Andrew Poulsom]

An example:

Private Sub ComboBox1_Change()
    With ListBox1
        .RowSource = ComboBox1.Value
        .ListIndex = -1
    End With
    TextBox1.Text = ""
End Sub

Private Sub ListBox1_Change()
    With Worksheets("Sheet1")
        TextBox1.Text = Range(ComboBox1.Value).Cells(ListBox1.ListIndex + 1).Offset(0, 1).Value
    End With
End Sub

The ComboBox contains the names of ranges which are used as the RowSource for ListBox1. TextBox1 will contain the contents of the cell immediately to the right of what's selected in ListBox1. To return other columns change the column offset in:

TextBox1.Text = Range(ComboBox1.Value).Cells(ListBox1.ListIndex + 1).Offset(0, 1).Value

 

[goldenvision]

Andrew,
Thanks for the quick response.

I have tried this and it isn't doing exactly what I expected. The combo and list boxes are populating correctly, however when I on item 1 in the listbox the text box is populated with listbox column 2. Click on item 2 in the listbox and the textbox is populated with listbox column 3 and so on.

David

(Code with my references in place)

Private Sub cbo_Marques_Change()
    With lst_S_Addr
        .RowSource = "Lookups!" & cbo_Marques
        .ListIndex = -1
    End With
    txt_SewellsSearchRef.Text = ""
End Sub
Private Sub lst_S_Addr_Change()
    With Worksheets("CLEANED SEWELLS")
        txt_SewellsSearchRef.Text = Range(cbo_Marques.Value).Cells(lst_S_Addr.ListIndex + 1).Offset(0, 1).Value
    End With
End Sub

 

[Andrew Poulsom]

Do you mean column or row? If your ListBox contains the items in the first column in:

a 10
b 20
c 30

selecting a will put 10 in TextBox1, b 20 etc.

 

[goldenvision]

Andrew,
Quick example of data

Combo box selects a range (i.e. Audi)
Range 'AUDI' consists of Ref #(this is unique), AddrLine1, Pcode
Listbox is populated with this information

Click on an item in the listbox and I want to ref # to be found in the mastersheet and a textbox populated with Dealer Name

 

[Andrew Poulsom]

If your range has more than one column, you will need something like:

txt_SewellsSearchRef.Text = Range(cbo_Marques.Value).Cells(lst_S_Addr.ListIndex + 1, 1).Offset(0, 1).Value

That should return AddrLine1 for the selected item.

I had only a single column so I omitted the column argument in Cells(ListBox1.ListIndex + 1).

 

[goldenvision]

Andrew,
Thanks that's great. The 'Ref' text box is now populated with the Unique ID #.
I can now use this as a search variable to search through the master table

Cheers
David