Locate and display data using listbox in vba

Question

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

score 3 · Accepted Answer · 2013-09-25 12:08:43Z

3

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

edited Sep 25, 2013 at 12:08

answered Sep 25, 2013 at 11:45

user2140173

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

3 Comments

user2123999 Over a year ago

@mehow..This is exactly what I'm looking for.And as a beginner the explanation made me understand how this code works..Thank you!

user2140173 Over a year ago

@user2123999 you are welcome! for more information on how to build userforms see this

user2123999 Over a year ago

@mehow..Gonna have more time reading this.Sure this will give me further information :D

Collectives™ on Stack Overflow

Locate and display data using listbox in vba

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related