Quote Originally Posted by mdmackillop
Have you tried the in-built DataForm?
I have now! I would still like to know how to code something similar in my own userform though as it won't be me doing to editing. Eventually I don't want the end user to have direct access to the raw data sheet.

I have the following code which populates the listbox based on the searching column A for either Yes or No :-

[vba]Private Sub cmdSearch_Click()
Dim foundArray As Variant
With Me
foundArray = ArrayOfMatchingRows(.txtActionedSearch.Text)
With .lbxResults
.Clear
If 0 < UBound(foundArray, 1) Then
.List = foundArray
End If
End With
End With
End Sub
Function ArrayOfMatchingRows(searchTerm As String) As Variant
Dim oneCell As Range
Dim ColumnCount As Long
Dim DataArray As Variant
Dim countOfFound As Long, i As Long

ColumnCount = 8
ReDim DataArray(1 To ColumnCount, 1 To dataRange.Rows.Count)

countOfFound = 0
For Each oneCell In dataRange().Columns(1).Cells
If LCase(oneCell.Value) = LCase(searchTerm) Then
countOfFound = countOfFound + 1
For i = 1 To ColumnCount
DataArray(i, countOfFound) = oneCell.Cells(1, i).Value
Next i
End If
Next oneCell

If 0 < countOfFound Then
ReDim Preserve DataArray(1 To ColumnCount, 1 To countOfFound)
Else
ReDim DataArray(0 To 0, 0 To 0)
End If

ArrayOfMatchingRows = Application.Transpose(DataArray)
End Function
Function dataRange() As Range
Set wsYBS = Worksheets("YBS")
With wsYBS.Range("A:A")
Set dataRange = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 8)
End With
End Function[/vba]

  • If there is only one matching record it lists the result in one column rather than in a row, how can i avoid this?
  • How do i get the header row from the worksheet "YBS", Range A1:A4 into the listbox as a header row?
  • Can the listbox results then be sorted by date?
I've attached an updated workbook.