Hi i have a search facility on a userform that when you specify a column to search in and type part of a word into a text box it will bring up all the records in the spreadsheet that relate to the search via the list view.
However i want to be able to click a button to show all records in the spreadsheet in the list view.
this is my code for the search facilty.
Private Sub OK_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
StartRow = 2
Col = header.ListIndex + 1
If Col = 0 Then
MsgBox "Please choose a Search category."
Exit Sub
End If
If sch.Text = "" Then
MsgBox "Please Enter a Search Criteria."
sch.SetFocus
Exit Sub
End If
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
Set Rng = Range(Cells(2, Col), Cells(LastRow, Col))
Set FoundMatch = Rng.find(What:=sch.Text, _
After:=Rng.Cells(1, 1), _
LookAt:=xlPart, _
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 9
Set C = 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 " & sch.Text
End If
End Sub
Private Sub UserForm_Activate()
Dim C As Long
Dim I As Long
Dim R As Long
ListView1.View = lvwReport
ListView1.HideSelection = False
ListView1.FullRowSelect = True
ListView1.HotTracking = True
ListView1.HoverSelection = False
ListView1.ColumnHeaders.add Text:="Row", Width:=64
For C = 1 To 9
ListView1.ColumnHeaders.add Text:=Cells(1, C).Text
header.AddItem Cells(1, C).Text
Next C
'For R = 2 To 21
' ListView1.ListItems.Add Index:=R - 1, Text:=Str(R)
' For C = 1 To 13
' ListView1.ListItems(R - 1).ListSubItems.Add Index:=C, Text:=Cells(R, C).Text
' Next C
'Next R
End Sub
Thanks in advance
L
Bookmarks