CODEWITHSUNDEEP
vbaexcel
user2123999
user2123999

Reputation: 37

Locate and display data using listbox in vba

I have tried this code to locate specific data from excel using List Box in VBA, It populated a list of names from sheet3 range(E7), then everytime I click an item/name on it the program should located the name from sheet3 and display the data on that row into their corresponding textboxes in my userform.But this doesn't work pecisely.Thanks.

Private Sub ListBox1_Click()
Dim isRow As Long
    If Me.ListBox1.ListIndex > -1 Then
        isRow = Me.ListBox1.ListIndex + 1
    End If
    Me.Label1 = Cells(sRow, 5) 
    Me.txt_Mon_in.Text = Cells(sRow,6)

End Sub

Populating data from Sheet3.

Private Sub Userform_Initialize()
Dim vCol As Variant
Dim Lrow As Long
    Lrow = Sheets("Sheet3").UsedRange.Rows(Sheets("Sheet3").UsedRange.Rows.Count).Row
    vCol = Sheets("Sheet3").Range("E7:E" & Lrow).Value
    Me.ListBox1.List = vCol

End Sub

Upvotes: 2

Views: 17350

Answers (1)

user2140173
user2140173

Reputation:

Im not quite sure what you are doing but try the below code

Private Sub Userform_Initialize()
    Dim vCol As Variant
    Dim Lrow As Long
    Lrow = Sheets("Sheet3").UsedRange.Rows(Sheets("Sheet3").UsedRange.Rows.Count).Row
    vCol = Sheets("Sheet3").Range("E7:E" & Lrow).Value
    Me.ListBox1.List = vCol
End Sub


Private Sub ListBox1_Click()

    Dim selectedName As String
    Dim i As Long
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                selectedName = .List(i)
            End If
        Next i
    End With
    Dim c As Range
    For Each c In Sheets(3).Range("E7:E" & Sheets(3).Range("E" & Rows.Count).End(xlUp).Row)
        If c = selectedName Then
            Label1 = Sheets("Sheet3").Cells(c.Row, 5)
            txt_Mon_in.Text = Sheets("Sheet3").Cells(c.Row, 6)
        End If
    Next c

End Sub

the Listbox1_Click() sub will iterate over the column E in sheet 3 and put the name in the Label1 control and will put offset of (0,1) of the found cell into the txt_Mon_in contol.

Sheet3

enter image description here

Userform

enter image description here

Result

enter image description here

Upvotes: 3

Related Questions