Excel Userform combobox linking

[Akbarov]

Hey dear community,

I have userform where user enter data to textbox and results are showing in userform labels, but entered name can have several match data. So instead of textbox i want to use combobox
User type few letter and matching names shows on cells Al2, Al3 , AL4 , AL5 .... AL12 and when user choose one of them on combobox, that name enters to z2 cell and labels showing result based on z2 data on labels ( label3 to label 23 )

 

[My Aswer Is This]

I may or may not be able to help but I will try. If I don't have a answer maybe someone else here at Mr. Excel will be able to help.

So are you saying you want to load all the value in your active sheet in Range("AL2:AL12")
Into your Combobox named Combobox1.
And then the value you select in the combobox you want this value loaded into Range("Z2") on your active sheet. Then you want the value in Range("Z2") loaded into your Labels named Label3 and Label4 and on and on to Label23.

Is this what you want?

If not please explain to me more.

 

[Akbarov]

Yes
only this part is wrong "Then you want the value in Range("Z2") loaded into your Labels named Label3 and Label4 and on and on to Label23."
Based on value in Z2, formulas will give me names on AL2:AL12 and users can choose any of names on combobox and based on choosen result active sheet will give details about that names

So when user type: " Jon " on combobox ( Z2 )
Formula will give mathcing results on AL2:AL12 :
Jonny
Jonsen
Jonatan

And that names will be on combobox list/ when user choose right name . Labels will fill all details about that name which are on Y1:Y20

 

[My Aswer Is This]

First you said all is correct but this but then on to say everything was wrong.

I want to know what you want loaded into Combobox
I want to know what you want loaded into labels

And can you show me where in post one you said anything about Y1 To Y20

This may be getting way to hard for me to understand.

 

[My Aswer Is This]

I must admit there are dozens of people here on this forum who can understand questions better then me. So I'm going to sit on the sidelines here and watch and hope someone else here will be able to help you.
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[My Aswer Is This]

Why can we not load all the values in range ("AL2:AL12") into the combobox and then you choose which one you want?
You earlier said

User type few letter and matching names shows on cells Al2, Al3 , AL4 , AL5 .... AL12 and when user choose one of them on combobox, that name enters to z2

The way I explained does exactly what you asked for. Now you never mentioned Y1 to Y20
​

 

[Akbarov]

Cell Y1-Y20 gives detailed info about choosen name. Name ,surname, job, phone num. etc.
AL2:AL12 only shows 10 ( max. ) matching names ( i have list of 1000 names )
to get matching names user need to type somethink and what he type will appear on Z2 and based on z2 value AL2:AL12 will give matching results

 

[jolivanes]

Change all references, sheets, cell addresses/ranges etc as required.
This uses a listbox, not a combobox.
You should be able to get the rest for your labels from your previous post.

Private Sub TextBox1_Change()

    Dim c, firstaddress, sh2 As Worksheet
    Set sh2 = Worksheets("Sheet2")
    ListBox1.Clear

    With sh2.Range("B1:B" & sh2.Cells(Rows.Count, 2).End(xlUp).Row)
        Set c = .Find(TextBox1.Text, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)
        If Not c Is Nothing Then
            firstaddress = c.Address
            Do
                ListBox1.AddItem c.Text

                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstaddress
        End If
    End With
End Sub

 

[jolivanes]

I found this and it is better.

Private Sub TextBox1_Change()
    'Thanks to AlphaFrog at Mr.Excel.com 532918 Listbox displaying multiple search results as you type
    Dim fCell As Range, fCells As Range
    Dim Firstfound As String
    
    ListBox1.Clear

    With Worksheets("Sheet2")
        Set fCells = .Range("B1", .Range("B" & Rows.Count).End(xlUp))
    End With
    
    Set fCell = fCells.Find(What:=TextBox1.Value & "*", _
                            After:=fCells(fCells.Count), _
                            LookIn:=xlValues, _
                            Lookat:=xlWhole, SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, MatchCase:=False)
                            
    If Not fCell Is Nothing Then
        Firstfound = fCell.Address
        Do
            With ListBox1
                .AddItem fCell.Value
                .List(.ListCount - 1, 1) = fCell.Value
            End With
            Set fCell = fCells.FindNext(After:=fCell)
        Loop While fCell.Address <> Firstfound
    End If

End Sub

 

[Akbarov]

i added code but now i can't enter data to listbox it is not clickable