Combobox alphabetical order and autofill

[Ana Luna]

Hello,
I am trying to add to a combobox the values in column B(name) in alphabetical order. Moreover, If I type a "h" in the combobox , must appear the values that starts with "h" (so, the combobox must have such a autofill property). After that, I want to display the information on the TextBox1(ref) and TextBox2(name) of the value selected in the combobox.

I show you below the code that I have tried but , regrettably, with no result.
You can find the excel file attached.
Thank you so much.Any help will be welcome.

>>> You've already noted <<<
>>> use code - tags <<<

Private Sub ComboBox1_Change()
    Dim searchText As String
    Dim cell As Range
    Dim selectedRow As Range

 

   ' Find the row corresponding to the selected value in ComboBox1.

    Set selectedRow = Worksheets("Hoja1").Columns("B").Find(What:=searchText, LookIn:=xlValues, LookAt:=xlWhole)

  
    ' Check if the corresponding row was found in Hoja1


   If Not selectedRow Is Nothing Then

        ' Fill the TextBoxes of TextBox1 and TextBox2 with the values from the row

       Set selectedRow = selectedRow.EntireRow

       UserForm1.TextBox1.Text = selectedRow.Cells(1, "A").value

       UserForm1.TextBox2.Text = selectedRow.Cells(1, "B").value

      

   Else

        ' Clear the TextBoxes if no match was found.

       UserForm1.TextBox1.Text = ""

       UserForm2.TextBox2.Text = ""

      

   End If
End Sub


Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
   
    ' Specify the name of the Excel sheet where column B is located.
    Set ws = ThisWorkbook.Worksheets("Hoja1")
   
    ' Specify the range of column B that contains the values.
    Set rng = ws.Range("B1:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
   
    ' Clear any existing value in ComboBox1
    Me.ComboBox1.Clear
   
    ' Loop through the values in column B and add them to ComboBox1.
    For Each cell In rng
        Me.ComboBox1.AddItem cell.value
    Next cell
   
    ' Sort the values of ComboBox1 alphabetically.
    Me.ComboBox1.List = Application.WorksheetFunction.Sort(Me.ComboBox1.List)
End Sub

>>> You've already noted <<<
>>> use code - tags <<<

 

[Marc L]

Hello, TextBox2 is useless as a duplicate of ComboBox1 … Anyway for starters :​

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex < 0 Then
        TextBox1 = ""
        TextBox2 = ""
    Else
        TextBox1 = ComboBox1.List(ComboBox1.ListIndex, 1)
        TextBox2 = ComboBox1   ' duplicate !
    End If
End Sub

Private Sub UserForm_Initialize()
    With Hoja1.[A1].CurrentRegion
        .Sort .Cells(2), 1, Header:=True
         ComboBox1.List = Application.Index(.Value, Evaluate("ROW(2:" & .Rows.Count & ")"), [{2,1}])
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Frank2021]

Hi Ana,

I enclosed my proposal.
Combobox's behavior is Match-Entry-First-Letter, so when first letter has been typed and match entry is found it will appear

Regards