Snasui Excel Forum

Code: Select all

Private Sub ComboBox1_change() 'Reference combobox to textbox
Dim i As Integer
Application.EnableEvents = False
i = 3
While Sheets("List").Cells(i, 1).Value <> ""
If Sheets("List").Cells(i, 1).Value = ComboBox1.Value Then
        'Range("Name") --> Name = Name manager are Range("A3:F7")
TextBox1.Value = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("list").Range("Name"), 2, False)
TextBox2.Value = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("list").Range("Name"), 3, False)
TextBox3.Value = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("list").Range("Name"), 4, False)
TextBox4.Value = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("list").Range("Name"), 5, False)
TextBox5.Value = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("list").Range("Name"), 6, False)
End If
i = i + 1
Wend
Application.EnableEvents = True
End Sub

Code: Select all

Private Sub ComboBox1_change() 'Reference combobox to textbox 1-5
    Dim rFind As Range, vTarget As Variant
    vTarget = ComboBox1.Value
    With ThisWorkbook.ActiveSheet
        Set rFind = .Columns(1).Find(what:=vTarget, after:=.Range("a2"), LookIn:=xlValues _
                , lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext _
                , MatchCase:=False)
        If rFind Is Nothing Then MsgBox "Not found", vbCritical: Exit Sub
        TextBox1.Text = rFind.Offset(0, 2)
        TextBox2.Text = rFind.Offset(0, 1)
        TextBox3.Text = rFind.Offset(0, 3)
        TextBox4.Text = rFind.Offset(0, 5)
        TextBox5.Text = rFind.Offset(0, 4)
    End With
End Sub