Text boxes populating from Combo box

[gadrummer65]

Hello,
I have a code that mostly works. The only problem i am having is when the text boxes populate, they always populate the last item in my list, and not what i am clicking on. Any help would be greatly appreciated.

Private Sub ComboBox2_Click()

    Dim LR As Long
    Dim fRng As Range

    With Sheets("Hotels")
        LR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row

        Set fRng = .Range("A2:A" & LR)

        If Not .Aut**ilterMode Then
            .Range("A3").Aut**ilter
        End If
        .Range("A3:A" & LR).Aut**ilter Field:=1, Criteria1:=Me.ComboBox2.Value
        Me.tbResCol1.Value = .Aut**ilter.Range.**fset(1).SpecialCells(xlCellTypeVisible).Cells(0, 2).Value
        Me.tbResCol2.Value = .Aut**ilter.Range.**fset(1).SpecialCells(xlCellTypeVisible).Cells(0, 3).Value
        Me.tbResCol3.Value = .Aut**ilter.Range.**fset(1).SpecialCells(xlCellTypeVisible).Cells(0, 4).Value
        Me.tbResCol4.Value = .Aut**ilter.Range.**fset(1).SpecialCells(xlCellTypeVisible).Cells(0, 5).Value
        Me.tbResCol5.Value = .Aut**ilter.Range.**fset(1).SpecialCells(xlCellTypeVisible).Cells(0, 6).Value
        .Aut**ilterMode = False
    End With
    Me.ComboBox1.Enabled = True
End Sub

 

[royUK]

What exactly are you trying to do? It seems a very complicated way to populate textboxes.

Please try sharing a dummy file using OneDrive, DropBox or similar

 

[gadrummer65]

How do i post a sample workbook to the thread?

 

[royUK]

On this Forum the only way is to use a File sharing site like DropBox. Post a link here.

 

[alansidman]

Crossposted to: Combo Box populating Text boxes

Please look at this link;

Excelguru Help Site - A message to forum cross posters

 

[royUK]

What does the AutoFilter find?

Is there more than one row?

I think possibly AdvancedFilter may be a better option.

 

[royUK]

Replace your code with this

Private Sub ComboBox2_Click()




    Dim fRng As Range, fCl As Range
    Dim lRw As Long
    Dim iX As Integer


    With Worksheets("Hotels")
        Set fRng = .Range("B2").CurrentRegion.Offset(1).Columns(1)
    End With


    With fRng
        Set fCl = .Find(Me.ComboBox1.Value)
        If Not fCl Is Nothing Then
            lRw = fCl.Row - 1
            For iX = 1 To 8
                Me("tbResCol" & iX).Value = .Cells(lRw, iX).Value
            Next iX
        End If
    End With


    Me.ComboBox1.Enabled = True
End Sub

 

[gadrummer65]

Thanks. Just one problem. None of the textboxes are populating with the information.

 

[royUK]

They all populate for me.

Sample WorkBook

 

[gadrummer65]

Your sample worked. I must of done something wrong. Thank you so much for all of your help.