Excel VBA + Fill multiple Text boxes based on values in ComboBox with or without loop

Question

Sir, i am trying very hard to solve this. Please help. I made many TextBoxes on user form like textbox1, 2, 3, 4, 5, 6, 7, 8 to 16. I have data for example like this I want to Put a name in Combobox and want to show First search result's Name in TextBox1, Age in textbox2 City in Textbox3, Then loop search same value in next cell, if Found then His name in Textbox 4, Age in Textbox5, City in Textbox 6 and so on (Maximum 4 result to be found). I am using following code but its showing only First found result in Textbox1, Textbox2 & Textbox3 but Second found result not popup in Textbox4, 5 & 6. Please help : Code is as follows

Private Sub CommandButton1_Click()
    Dim fnd As String, FirstFound As String
    Dim FoundCell As Range, rng As Range
    Dim myRange As Range, LastCell As Range
    Dim a, b, c As Integer

    a = 1
    b = 2
    c = 3

    fnd = ComboBox1.Value

    Set myRange = Sheets("Data").Range("B4:B50")
    Set LastCell = myRange.Cells(myRange.Cells.Count)
    Set FoundCell = myRange.Find(What:=fnd, After:=LastCell)

    If Not FoundCell Is Nothing Then
        FirstFound = FoundCell.Address
    Else
        GoTo NothingFound
    End If

    Set rng = FoundCell

    Do Until FoundCell Is Nothing
        UserForm1.Controls("TextBox" & a).Text = FoundCell.Offset(0, 0).Value
        UserForm1.Controls("TextBox" & b).Text = FoundCell.Offset(0, 1).Value
        UserForm1.Controls("TextBox" & c).Text = FoundCell.Offset(0, 2).Value                     
        a = a + 3
        b = b + 3
        c = c + 3

        If FoundCell.Address = FirstFound Then Exit Do
    Loop

    Exit Sub

NothingFound:
    TextBox1.Text = "not found"
    TextBox2.Text = "not found"
    TextBox3.Text = "not found"
End Sub

Please help me to sort this out. I had searched hundreds of website but stuck at this point. Thanks a lot in Advance

Thanks for the solution

Mohd Asif
– Mohd Asif

2017-02-08 08:51:36 +00:00
Commented Feb 8, 2017 at 8:51 — Mohd Asif
– Mohd Asif, Commented Feb 8, 2017 at 8:51

user3598756 · Accepted Answer · 2017-02-04 11:27:39Z

0

you could use AutoFilter(), assuming that cell "B3" has a header:

Option Explicit

Private Sub CommandButton1_Click()
    Dim fnd As String
    Dim txtBoxOffset As Long, j As Integer
    Dim cell As Range

    If Me.ComboBox1.ListIndex = -1 Then Exit Sub

    fnd = Me.ComboBox1.Value
    With Sheets("Data").Range("B3:B50")
        .AutoFilter field:=1, Criteria1:=fnd
        If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then
            For Each cell In .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible)
                For j = 1 To 3
                    Me.Controls("TextBox" & (txtBoxOffset + j)).Text = cell.Offset(, j - 1)
                Next
                txtBoxOffset = txtBoxOffset + 3
            Next
        Else
            For j = 1 To 3
                Me.Controls("TextBox" & j).Text = "not found"
            Next
        End If
        .Parent.AutoFilterMode = False
    End With
End Sub

answered Feb 4, 2017 at 11:27

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3598756 Over a year ago

@MohdAsif, did you get through it?

Mohd Asif Over a year ago

Thanks for the solution

user3598756 Over a year ago

You are welcome. And if you are concerned with the "solution length" issue, then consider that what above is all the code you need, i.e. its substitutes your entire code in OP

A.S.H · Accepted Answer · 2017-02-04 11:28:56Z

0

You're not fectching the next records. Here's a quick-fix of your loop:

    Do
        UserForm1.Controls("TextBox" & a).Text = FoundCell.Offset(0, 0).value
        UserForm1.Controls("TextBox" & b).Text = FoundCell.Offset(0, 1).value
        UserForm1.Controls("TextBox" & c).Text = FoundCell.Offset(0, 2).value
        a = a + 3
        b = b + 3
        c = c + 3
        Set FoundCell = myRange.Find(What:=fnd, After:=FoundCell) ' <-- get next record
    Loop Until FoundCell.Address = FirstFound '<-- Stop when retrieveing again the same first record

edited Feb 4, 2017 at 11:28

answered Feb 4, 2017 at 11:21

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Collectives™ on Stack Overflow

Excel VBA + Fill multiple Text boxes based on values in ComboBox with or without loop

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related