Solved: Search form

[matty25]

Hi All,

I have the following code on frmSearch (userform) which i need to add some code to so that it presents a msgbox error if no results found when i click the findall button e.g. msgbox "No search results found", the search is defined by the user inputted text/string inputted in TextBox1 and the results are shown/filtered in ListBox1.

Private Sub UserForm_Initialize()
TextBox1.SetFocus
ListBox1.ColumnCount = 2
    Set myData = Sheet1.Range("a2").CurrentRegion
    With Me
        .Caption = "Search Database"
    End With
End Sub

Sub cmbFindAll_Click()
Dim strFind As String
Dim rFilter As Range
Dim ii As Long
Dim c As Range, a() As String, n As Long
Set rFilter = Sheet1.Range("a2", Range("a65536").End(xlUp))
strFind = Me.TextBox1.Value
With Sheet1
    If Not .AutoFilterMode Then .Range("A2").AutoFilter
    rFilter.AutoFilter Field:=1, Criteria1:="*" & strFind & "*"
        Set rng = Sheet1.Range("a2", Range("a65536").End(xlUp))
        Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
    For Each c In rng
        n = n + 1: ReDim Preserve a(0 To 11, 1 To n)
        For ii = 0 To 11
           a(ii, n) = c.Offset(, ii).Value
        Next
    Next
End With
If n > 0 Then Me.ListBox1.Column = a
End Sub

Any help you can offer would be much appreciated.

Many thanks, Matt

[fb7894]

[vba]
Sub FindFunction()
Dim rFound As Range
On Error Resume Next

With Sheet1
Set rFound = .Columns(1).Find(What:="Cat", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

, SearchFormat:=False)

On Error GoTo 0

If Not rFound Is Nothing Then
MsgBox "No Records Found"
End With

End Sub
[/vba]

[fb7894]

[vba]
Sub FindFunction()
Dim rFound As Range
On Error Resume Next

With Sheet1
Set rFound = .Columns(1).Find(What:="Cat", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

, SearchFormat:=False)

On Error GoTo 0

If Not rFound Is Nothing Then
MsgBox "No Records Found"
End With

End Sub
[/vba]

[fb7894]

sorry, posted twice by mistake.

[matty25]

[vba]
Sub FindFunction()
Dim rFound As Range
On Error Resume Next

With Sheet1
Set rFound = .Columns(1).Find(What:="Cat", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

, SearchFormat:=False)

On Error GoTo 0

If Not rFound Is Nothing Then
MsgBox "No Records Found"
End With

End Sub
[/vba]

Thanks fb, but the highlighted text above is coming up as a syntax error in red, any ideas? Thanks for your help so far.

[mdmackillop]

[VBA]Sub FindFunction()
Dim rFound As Range
On Error Resume Next
With Sheet1
Set rFound = .Columns(1).Find(What:="Cat", After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
On Error GoTo 0
If rFound Is Nothing Then
MsgBox "No Records Found"
End If
End With
End Sub
[/VBA]

[matty25]

A real big thank you for your replies.

Here is my full code for the search form. Not sure why but when the text user entered in TextBox1 doesnt match/find any results the error msgbox isnt popping up saying no results found, instead it just shows "Trade" and "Company Name" on one line in the listbox as a search result.

Option Explicit
Dim MyArray(6, 4)
Public myData As Range, c As Range
Dim rFound     As Range
Dim r          As Long
Dim rng        As Range
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub CommandButton3_Click()
With ThisWorkbook.Worksheets("Sheet1")
If .FilterMode Then
.ShowAllData
TextBox1.Text = ""
TextBox1.SetFocus
ListBox1.Clear
End If
End With
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Load frmRecord
    frmRecord.TextBox1.Value = Me.ListBox1.Column(1)
    frmRecord.TextBox2.Value = Me.ListBox1.Column(2)
    frmRecord.TextBox3.Value = Me.ListBox1.Column(3)
    frmRecord.TextBox4.Value = Me.ListBox1.Column(4)
    frmRecord.TextBox5.Value = Me.ListBox1.Column(5)
    frmRecord.TextBox6.Value = Me.ListBox1.Column(6)
    frmRecord.TextBox7.Value = Me.ListBox1.Column(7)
    frmRecord.TextBox8.Value = Me.ListBox1.Column(8)
    frmRecord.TextBox9.Value = Me.ListBox1.Column(9)
    frmRecord.TextBox10.Value = Me.ListBox1.Column(10)
    frmRecord.TextBox11.Value = Me.ListBox1.Column(11)
    frmRecord.Show
End Sub
Private Sub UserForm_Initialize()
TextBox1.SetFocus
ListBox1.ColumnCount = 2
    Set myData = Sheet1.Range("a2").CurrentRegion   'database
    With Me
        .Caption = "Search Database"    'userform caption
    End With
End Sub
Sub cmbFindAll_Click()
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
Dim ii As Long
Dim c As Range, a() As String, n As Long
Set rFilter = Sheet1.Range("a2", Range("a65536").End(xlUp))
strFind = Me.TextBox1.Value
With Sheet1
    If Not .AutoFilterMode Then .Range("A2").AutoFilter
    rFilter.AutoFilter Field:=1, Criteria1:="*" & strFind & "*"
        Set rng = Sheet1.Range("a2", Range("a65536").End(xlUp))
        Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
    For Each c In rng
        n = n + 1: ReDim Preserve a(0 To 11, 1 To n)
        For ii = 0 To 11
           a(ii, n) = c.Offset(, ii).Value
        Next
    Next
End With
If n > 0 Then Me.ListBox1.Column = a
End Sub
Sub FindFunction()
    Dim rFound As Range
    On Error Resume Next
    With Sheet1
        Set rFound = .Columns(1).Find(What:="Cat", After:=.Cells(1, 1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        On Error GoTo 0
        If rFound Is Nothing Then
            MsgBox "No Records Found"
        End If
    End With
End Sub

Any help you can offer would be much appreciated.

Cheers, Matt

[mdmackillop]

Hi Matt,
Can you post a workbook containing your form?

[matty25]

Hi

Here's my workbook:

http://rapidshare.com/files/16066681..._List.xls.html

Cheers, Matt

[mdmackillop]

Apologies Matt,
I responded correcting fb's code, not your original query
try
[VBA]Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
If rng.Row = 1 Then
MsgBox "Nothing found"
End If [/VBA]

[matty25]

Excellent, thank you so much, i amended the code slightly to this which works a treat:

Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
If rng.Row = 1 Then
    ListBox1.Clear
    MsgBox "No records found"

Many thanks, Matt

[lucas]

Matt, please mark your thread solved using the thread tools at the top of the page.