Text boxes populating from Combo box

gadrummer65

New Member
Joined
Mar 6, 2014
Messages
9
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.

Code:
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

Well-known Member
Joined
Jul 24, 2002
Messages
1,605
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
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,605
On this Forum the only way is to use a File sharing site like DropBox. Post a link here.
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,605
What does the AutoFilter find?

Is there more than one row?

I think possibly AdvancedFilter may be a better option.
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,605
Replace your code with this

Code:
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
 

Forum statistics

Threads
1,085,576
Messages
5,384,542
Members
401,906
Latest member
IvayloKonsulov

Some videos you may like

This Week's Hot Topics

Top