Search and List results

[LittleRedOne]

Hi All,

I am fairly new to VBA and am trying to create a automated spreadsheet to record and display. I have managed so far to source and amend codes accordingly todo this but I have hit a wall.

As part of the spreadsheet I would like to include an issues log, whereby all problems that have not had a resolution date can be seen in a listbox and then the individual issues can be selected and viewed in a different user form.

I need to search column J of the spreadsheet for blank cells (within a range of rows that will keep growing) and then display the results of column B, H & I in a listbox. I would then like to be able to click any one of these results to display all the data in userform. Please can someone help me?

Many Thanks in advance

 

[mikerickson]

Code like this will display the filtered results in the ListBox. (The Initialize event sets properties that can be set at design time.)
Note that the fourth, invisible, column of the ListBox holds the address of the row from which that data came.

Sub FillListBox()
    Dim dataRange As Range, oneCell As Range
    
    With Range("B:B")
        Set dataRange = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
    
    Me.ListBox1.Clear
    For Each oneCell In dataRange
        With oneCell.EntireRow
            If CStr(.Range("j1").Value = vbNullString) Then
                ListBox1.AddItem CStr(.Range("B1").Value)
                ListBox1.List(ListBox1.ListCount - 1, 1) = CStr(.Range("H1").Value)
                ListBox1.List(ListBox1.ListCount - 1, 2) = CStr(.Range("I1").Value)
                ListBox1.List(ListBox1.ListCount - 1, 3) = .EntireRow.Address(, , , True)
            End If
        End With
    Next oneCell

End Sub

Private Sub UserForm_Initialize()
    Rem these properties can be set at design time
    With ListBox1
        .ColumnCount = 4: Rem col B, col H, col I, address
        .ColumnWidths = "50;50;50;0"
    End With
End Sub

 

[LittleRedOne]

Hi Mike,

Thanks for the reply, the code works great. I did amend it slightly to remove some bugs i.e identify the listbox as being in userform2. But other than that it displays the info just as requested. Thank you so much, I would not have been able to get there on my own.

Is there any way to wrap the text in the listbox columns?

Also how would I know select one entry from the list box and display all the data from that row in my predefined userform 1?