Choose item in Listbox then translate to textbox/combobox

[daithiboy]

Evening All,

I would be very appreciative if you can give me some sage advice, I'll do my best to explain.

I have a user form with textbooks and comboboxes for "First Name", "Last Name" and "ID Number". I also have a "Search" command button which, when a ID number is entered, will search for the corresponding names and populate the text/combo boxes. Clicking a "Save" command button, tosses all the info onto a sheet.

I have a filter set up so I can search for a name, by inputting into the "Name" textbook and all of the available options show in a listbox, which is also on the form e.g. if I search for "Dave" all of the Daves in the company will populate in the listbox.

It would be fantastic if I were able to select the desired "Dave" from the listbox and either copy the info to the sheet (as if I had clicked "Save"), or takes the data from the listbox and inputs it into the text/combo boxes on the userform and I can then press the "Save" command button.

So in summary, I choose Dave Spencer 11233 from the listbox and the user form populates as follows

(Textbox)First Name = "Dave"
(Textbox)Last Name = "Spencer"
(Combo) ID Number = "11233"

Click command button "Save" and it is all transferred to my desired worksheet.

I hope I explained it well and i am sorry to say that I do not currently have access to my code!!

Many thanks,
Dave

 

[Norie]

Dave

How are you populating the textbox with the results of the filter?

 

[daithiboy]

Dave

How are you populating the textbox with the results of the filter?

Hey Norie,

I'm populating a listbox with the results of the filter. Essentially, it's an advanced filter that puts the results into a table, which populates the listbox using row source.

Hope that clarifies.

Dave

 

[Norie]

Dave

The population from a filtered list is clear but how you are actually doing that isn't, well not totally anyway.

Are you copying the result of the advanced filter to a new range?

If so are you using List to populate the listbox from that range?

Perhaps AddItem, or even RowSource - all viable options.

 

[daithiboy]

Hey Norie,

You're spot on. The filtered list is copied to a table, which is the RowSource for the listbox. When I'm finished searching, I have a "Clear" button which clears the contents of the table and thus the listbox also.

My goal is to be able to populate the listbox (already done) and then select a row in the listbox. With the selected listbox row, I would like to either take the info and place it into corresponding text/combo boxes on the same user form; or take some of the info from the selected listbox row and copy it to a worksheet.

Cheers,
Dave

 

[MrIfOnly]

Hi Dave:

You could try something like this:

Make your textboxes and combobox names: Info1, Info2, Info3 respectively.

Then add the following to your Userform code:

Private Sub Listbox1_Click()
Dim x As Integer
With Listbox1
    For x = 1 To 3
        .TextColumn = x
        Me.Controls("Info" & x).Value = .Text
    Next x
End With
End Sub

Regards,

CJ

 

[daithiboy]

Thanks CJ,

Apologies for the delay. I think the best way for me to proceed is to have a command button that will take some of the info from the row I have selected in the listbox and copy it to my sheet. Is that possible? Norie, you seem to be the man to talk to about Listboxes.

Does that sound possible?

 

[MrIfOnly]

Sure it's possible. Here is one way: (since this may overwrite existing data you should try this in a copy of your workbook.)

Private Sub CommandButton1_Click()
Dim x As Integer
With ListBox1
    For x = 1 To 3
        .TextColumn = x
        Worksheets("Sheet2").Cells(2, x).Value = .Text
    Next x
End With
End Sub

Change CommandButton1 to the name of your command button. Change the worksheet name within quotations to your target worksheet.

As is, this will populate the 1st three columns of the 2nd row on sheet 2 with the data in the 1st three columns of the selected row of the listbox. If you want to populate say columns 4 thru 7 (D thru F), change the line:

Worksheets("Sheet2").Cells(2, x).Value = .Text

to

Worksheets("Sheet2").Cells(2, x + 3).Value = .Text

If the cells you wish to populate are noncontiguous then let me know and I can modify the code accordingly.

Regards,

CJ

 

[daithiboy]

Hey CJ,

Thanks for your detailed response and offer for further help. You really went above and beyond.

I haven't had a chance yet to try your suggestion, but last night I was fiddling around and put this together based on a forum post I found:

Private Sub lb_attendance_dblClick(ByVal Cancel As MSForms.ReturnBoolean)


Dim x As Long
     
    With lb_attendance
        For x = .ListCount - 1 To 1 Step -1
            If .Selected(x) Then
                DoEvents
                UserForm1.cb_id.Value = .List(x, 1)
                UserForm1.cb_name.Value = .List(x, 2)
                UserForm1.cb_lastname.Value = .List(x, 3)
                UserForm1.cb_grade.Value = .List(x, 8)
                UserForm1.cb_dept.Value = .List(x, 6)
                UserForm1.cb_gradecat.Value = .List(x, 9)
            End If
        Next x
    End With


End Sub

This works beautifully, but my only issue is that when I double click on the top row of the listbox nothing happens. All of the other rows work perfectly when double clicked.

Any thoughts on what my issue is would be greatly appreciated?

Many thanks,
Dave

 

[MrIfOnly]

Change this line:

For x = .ListCount - 1 To 1 Step -1

to read:

For x = .ListCount - 1 To [COLOR=#ff0000]0[/COLOR] Step -1

Regards,

CJ