List Results in Userform

Hi all,

I have done my best to create the worksheet attached making some sacrifices at the same time, for example on UserForm1 I was unable to get this to create a sequential reference number using "SBCRXXXX" so have gone for a random, which is ok as long as it doesn't repeat.

My biggest problem at present is USERFORM3, that the search option whilst it works by giving a row number doesn't display all the information I want from the row. I have so far spent a number of days using different tips and tricks I could find to get this working in its current state, is anyone able to please have a look at the coding and see why this might be?

Private Sub CommandButton1_Click()
'SEARCH

Dim Cnt As Long
Dim Col As Variant
Dim FirstAddx As String
Dim FoundMatch As Range
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long
Dim Wks As Worksheet

StartRow = 2
Set Wks = Sheets(1)

Col = ComboBox1.ListIndex + 1
If Col = 0 Then
MsgBox "Please choose a category to search."
Exit Sub
End If

If TextBox1.Text = "" Then
MsgBox "Please enter a search term."
TextBox1.SetFocus
Exit Sub
End If

LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
LastRow = IIf(LastRow < StartRow, StartRow, LastRow)

Set rng = Wks.Range(Wks.Cells(2, Col), Wks.Cells(LastRow, Col))

Set FoundMatch = rng.Find(What:=TextBox1.Text, _
After:=rng.Cells(1, 1), _
LookAt:=xlWhole, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not FoundMatch Is Nothing Then
FirstAddx = FoundMatch.Address
ListView1.ListItems.Clear

Do
Cnt = Cnt + 1
R = FoundMatch.Row
ListView1.ListItems.Add Index:=Cnt, Text:=R
For Col = 1 To 26
Set C = Wks.Cells(R, Col)
ListView1.ListItems(Cnt).ListSubItems.Add Index:=Col, Text:=C.Text
Next Col
Set FoundMatch = rng.FindNext(FoundMatch)
Loop While FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing
SearchRecords = Cnt
Else
ListView1.ListItems.Clear
SearchRecords = 0
MsgBox "No match found for " & TextBox1.Text
End If

End Sub

Private Sub ListView1_BeforeLabelEdit(Cancel As Integer)

End Sub

Private Sub UserForm_Activate()

Dim C As Long
Dim I As Long
Dim R As Long
Dim Wks As Worksheet

With ListView1
.Gridlines = True
.View = lvwReport
.HideSelection = False
.FullRowSelect = True
.HotTracking = True
.HoverSelection = False
.ColumnHeaders.Add Text:="Job Sheet", Width:=64
End With

Set Wks = Sheets(1)

For C = 1 To 13
ListView1.ColumnHeaders.Add Text:=Wks.Cells(1, C).Text
ComboBox1.AddItem Wks.Cells(8, C).Text
Next C

End Sub

Thank you very much in advance,

Daniel

Hi,

When I say no information, all I get is the row number even with the change of columns in the code. They sit side by side and do not show the row of information in full as a result.

I have attached a screenshot of the result I receive.

results.jpg

There are no lines or anything that appear and the full row of information is what I am trying to get this to display, including multiple results.

Hope that makes sense?

Thanks
Daniel

Originally Posted by mike7952

Try out the attached file, Uses adodb to query your Job Sheet. Loads combobox2 with values from sheet from what is selected combox1. Click search.

Hi Mike,
This works great! Only problem I have is when this is uploaded to One Drive as it is shared between the two of us the search key cannot find the file.
Is there any fix around this?
Thanks
Dan