Search and List results

LittleRedOne

New Member
Joined
Nov 28, 2013
Messages
4
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,773
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.


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

New Member
Joined
Nov 28, 2013
Messages
4
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?
 

Forum statistics

Threads
1,081,513
Messages
5,359,225
Members
400,521
Latest member
smarty1995

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top