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]

daithiboy

Is your listbox multiselect?

If it isn't then there's no need to loop, you can find out which row in the listbox has double clicked using ListIndex.

Private Sub lb_attendance_dblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim idx As Long
     
    With lb_attendance
        idx = .ListIndex
        UserForm1.cb_id.Value = .List(idx, 1)
        UserForm1.cb_name.Value = .List(idx, 2)
        UserForm1.cb_lastname.Value = .List(idx, 3)
        UserForm1.cb_grade.Value = .List(idx, 8)
        UserForm1.cb_dept.Value = .List(idx, 6)
        UserForm1.cb_gradecat.Value = .List(idx, 9)
    End With

End Sub

 

[daithiboy]

CJ,

Man, I wish I understood the basis of these things!!

That obviously worked perfectly. Thank you very much.

I think I owe you $9!!

many thanks,
Dave

 

[MrIfOnly]

Hi Dave:


You are welcome. I'm glad it's working for you. However, as Norie correctly pointed out, the looping method you are using is probably unnecessary and inefficient.

Regards,

CJ

 

[daithiboy]

Norie, CJ

Thanks a million for all of your help. Do either of you guys know of any tutorials or information I could look at for listboxes? Anything I have seen is very basic and I can't find anything that goes into any kind of depth, such as the solutions you offered for my problem.

Thanks again for your help.

Dave