ListBox does not display the record I try to find

[Magic Polygon]

I would like to post my Excel Spreadsheet, but I don't know if it is possible. Please let me know what I could do to better ask my question. Below is an attempt at giving context to my problem. With the initial data, SearchResultsListBox ends up having one item, but the item is empty.

BrowseTextBox.Value is "Alpha"
BrowseByComboBox.Value is "First Name"
Column B is the column for "First Name"

The image is part of "AppointmentsTable" table in the "Appointments" worksheet.

Dim SearchTerm As String
Dim SearchColumn As String
Dim RecordRange As Range
Dim FirstAddress As String
Dim FirstCell As Range
Dim RowCount As Integer
Dim SearchListBoxArray() As Variant
Dim SearchArrayColumn As Integer

' Display an error if no search term is entered
If BrowseTextBox.Value = "" Then
    MsgBox "No search term specified", vbCritical + vbOKOnly
    Exit Sub
End If

' Determine the search criteria based on the combo box value
Select Case BrowseByComboBox.Value

    Case "First Name", "Surname", "Appointment", "Next Appointment", "Dog Name", "Breed"
        SearchTerm = BrowseTextBox.Value
        SearchColumn = BrowseByComboBox.Value
       
    Case Else
        ' Handle unknown or invalid search criteria here
        MsgBox "Please choose a term to browse by."
        Exit Sub
       
End Select

SearchResultsListBox.Clear

' Only search in the relevant table column i.e. if somone is searching Location
' only search in the Location column
With Worksheets("Appointments").ListObjects("AppointmentsTable").ListColumns(SearchColumn).Range

    ' Find the first match
    Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)

    ' If a match has been found
    If Not RecordRange Is Nothing Then

        'Cell address of the first match
        FirstAddress = RecordRange.Address
       
        'Initialise the row count
        RowCount = -1
       
        'Determine how many rows there will be
        Do
       
            RowCount = RowCount + 1
           
            'Look for next match
            Set RecordRange = .FindNext(RecordRange)
       
        Loop While RecordRange.Address <> FirstAddress
       
        'Resize SearchListBoxArray
        ReDim Preserve SearchListBoxArray(0 To RowCount, 0 To 12)
       
        'Reinitialise the row count
        RowCount = -1
       
        'Reset RecordRange to be the first match
        Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)
       
        Do
       
            'Create a new row for ListBox
            RowCount = RowCount + 1
           
            'Set the first cell in the row of the matching value
            Set FirstCell = Range("B" & RecordRange.Row)
           
            ' Add an item to the List Box
            SearchResultsListBox.AddItem
           
            For SearchArrayColumn = 0 To 12
               
                'Populate the array
                SearchListBoxArray(RowCount, SearchArrayColumn) = FirstCell(1, SearchArrayColumn + 1)
           
            Next SearchArrayColumn
           
            ' Look for next match
            Set RecordRange = .FindNext(RecordRange)
       
            ' When no further matches are found, exit the sub
            If RecordRange Is Nothing Then
       
                'Format and populate list box using built array
                SearchResultsListBox.ColumnHeads = True
                SearchResultsListBox.ColumnCount = 13
                SearchResultsListBox.ColumnWidths = "50,50,50,50,50,50,50,50,50,50,50,50,50"
                SearchResultsListBox.List = SearchListBoxArray
       
                Exit Sub
       
            End If
       
        ' Keep looking while unique matches are found
        Loop While RecordRange.Address <> FirstAddress
       
    Else
   
        ' If you get here, no matches were found
        SearchResultsListBox.AddItem
        SearchResultsListBox.List(0, 0) = "Nothing Found"
   
    End If

End With

 

[jolivanes]

So what is your question?

 

[Akuini]

I would like to post my Excel Spreadsheet, but I don't know if it is possible.

You can use XL2BB tools.

 

[Magic Polygon]

So what is your question?

I was expecting the ListBox to have a multicolumn item representing all the data from B3 to N3. Instead, I'm sure it does have an item, but the item isn't populated with any data:

So, I'm wondering what is wrong with my VBA code.

 

[Magic Polygon]

You can use XL2BB tools.

That looks handy, I hope it works with Userforms.

 

[Akuini]

I hope it works with Userforms.

No, it doesn't. It's just to copy a range (with its formula & format) and paste it to the thread, and vice versa.
If you want to provide an example of your userform, you can upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here. Also, ensure that the link is accessible to anyone.

 

[Magic Polygon]

No, it doesn't. It's just to copy a range (with its formula & format) and paste it to the thread, and vice versa.
If you want to provide an example of your userform, you can upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here. Also, ensure that the link is accessible to anyone.

Thank you for the tip

 

[Magic Polygon]

Userform Mr Excel.xlsm

Shared with Dropbox

www.dropbox.com

Here is the link to my Userform. The relevant code is found under VBAProject -> Forms -> BrowseRecordsUserForms, within the DisplayRecords Sub. To run the procedure with the inputs that failed:

1. On the "Sheet1" sheet, click "Click here to open the appointment entry userform".
2. Click the "Browse Records" button on the opened userform.
3. Click the "First Name" item within the drop-down menu of the newly opened userform.
4. Type "Alpha" into the textbox that is on the right of the drop-down menu.
5. Click the "Browse" button.

Within the ListBox, I expected to see a row of data representing the range B3 to N3 in the "Appointments" sheet. Instead, it appear to have a row that is empty.

I hope this clarifies things and makes things more navigable.

 

[Magic Polygon]

Bump

 

[Magic Polygon]

A mistake I made was in referencing the range assigned to First Cell, below is the correction:

'Set the first cell in the row of the matching value
Set FirstCell = Worksheets("Appointments").Range("B" & RecordRange.Row)

Also, I made a mistake in my condition to assign the array to the ListBox, below is the correction:

'When no further matches are found, assign the array to the ListBox,
'format the ListBox, and exit the sub
If RecordRange.Address = FirstAddress Then

    SearchResultsListBox.ColumnHeads = True
    SearchResultsListBox.ColumnCount = 13
    SearchResultsListBox.ColumnWidths = "50,50,50,50,50,50,50,50,50,50,50,50,50"
    SearchResultsListBox.List = SearchListBoxArray

    Exit Sub

End If