Forum

Show full row in us...
 
Notifications
Clear all

Show full row in userform search

7 Posts
3 Users
0 Reactions
180 Views
(@stuartallison)
Posts: 2
New Member
Topic starter
 

I used the code in the tutorial below to make a search box for my table. Everything runs fine except the results. Rather than showing the full row, it is only showing the result in column A. I made sure the names of columns, search box, text box etc. all match so not too sure where i'm going wrong. 

 

In the downloaded spreadsheet it shows columns A - D, i need it to show A - G and from what i can tell it should be working. Any help would be appreciated. 

 

https://www.myonlinetraininghub.com/display-all-matches-from-search-in-userform-listbox - link to tutorial 

My code for search button:

 

Private Sub SearchBtn_Click()
Dim SearchTerm As String
Dim SearchColumn As String
Dim RecordRange As Range
Dim FirstAddress As String
Dim FirstCell As Range
Dim RowCount As Integer

' Display an error if no search term is entered
If Machine.Value = "" And SerialNumber.Value = "" And Location.Value = "" And Company.Value = "" And ContactNumber.Value = "" And PersonToContact.Value = "" And Email.Value = "" Then

MsgBox "No search term specified", vbCritical + vbOKOnly
Exit Sub

End If

' Work out what is being searched for
If Machine.Value <> "" Then

SearchTerm = Machine.Value
SearchColumn = "Machine"

End If

If SerialNumber.Value <> "" Then

SearchTerm = SerialNumber.Value
SearchColumn = "Serial Number"

End If

If Location.Value <> "" Then

SearchTerm = Location.Value
SearchColumn = "Location"

End If

If Company.Value <> "" Then

SearchTerm = Company.Value
SearchColumn = "Company"

End If

If ContactNumber.Value <> "" Then

SearchTerm = ContactNumber.Value
SearchColumn = "Contact Number"

End If

If PersonToContact.Value <> "" Then

SearchTerm = PersonToContact.Value
SearchColumn = "Person To Contact"

End If

If Email.Value <> "" Then

SearchTerm = Email.Value
SearchColumn = "Email"

End If
Results.Clear

' Only search in the relevant table column i.e. if somone is searching Location
' only search in the Location column
With Range("Table1[" & SearchColumn & "]")

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

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

FirstAddress = RecordRange.Address
RowCount = 0

Do

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

' Add matching record to List Box
Results.AddItem
Results.List(RowCount, 0) = FirstCell(1, 1)
Results.List(RowCount, 1) = FirstCell(1, 2)
Results.List(RowCount, 2) = FirstCell(1, 3)
Results.List(RowCount, 3) = FirstCell(1, 4)
Results.List(RowCount, 4) = FirstCell(1, 5)
Results.List(RowCount, 5) = FirstCell(1, 6)
Results.List(RowCount, 6) = FirstCell(1, 7)
RowCount = RowCount + 1

' Look for next match
Set RecordRange = .FindNext(RecordRange)

' When no further matches are found, exit the sub
If RecordRange Is Nothing Then

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
Results.AddItem
Results.List(RowCount, 0) = "Nothing Found"

End If

End With
End Sub

 
Posted : 05/03/2021 7:42 am
(@debaser)
Posts: 837
Member Moderator
 

Did you change the listbox's ColumnCount property to 7?

 
Posted : 05/03/2021 8:11 am
(@stuartallison)
Posts: 2
New Member
Topic starter
 

I knew it would be something obvious, but i didn't think it would be that obvious... Thanks, much apprecaited. 

 
Posted : 05/03/2021 9:05 am
(@debaser)
Posts: 837
Member Moderator
 

It's always the little things that are the easiest to overlook. 🙂

 
Posted : 05/03/2021 10:31 am
(@tushardahake)
Posts: 2
New Member
 

Hi,

I am facing the same issue with the column counts. I used the tutorial code to suit my needs. I have 40 columns in the table & i am not able to display more than 10. The code works fine till the column count is 10. I have given the coloumcount as 40 in the property box of the listbox as per you above suggestion.

But as soon as the below code is added, i get an error "Could not set the List Property. Invalid Property Value"

Results.List(RowCount, 10) = FirstCell(1, 11)

Where am i going wrong? Or is there a limit set to 10 columns?

In addition, I have 2 more queries:

1. Is there a way to show the column headers in the listbox

2. How to modify the code to search by date, as one of my columns has dates in it with DD-MMM-YY format

Thanks in advance.

 
Posted : 09/04/2021 3:06 am
(@debaser)
Posts: 837
Member Moderator
 

If you use AddItem you are limited to 10 columns. The alternatives are to use an array and assign that to the control, or to use a range on a worksheet. Since you want column headers, the only simple option is a range on a worksheet and pass the address of the range to the Rowsource property of the listbox.

 
Posted : 09/04/2021 5:23 am
(@tushardahake)
Posts: 2
New Member
 

Thanks Velouria.

Could you help me with the syntax of how to do it? Since I am new to VBA, i am unable to do so.

 
Posted : 09/04/2021 6:48 am
Share: