search through text boxes and show results in listbox in a userform

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
In my program I have a userform2 with four textboxes named CodeA, CodeB, CodeC and CodeD and a listbox named "Results", and a command button to execute the code.
I am calling this userform2 through another main userform1.

The four text boxes are used to search four columns - column1, 2, 4 and 7 of sheet2 respectively.

Table1 is the name of the range Sheet2!$A$2:$O$999999

These columns in sheet2 have the heading as ColumnCodeA, ColumnCodeB, ColumnCodeC, ColumnCodeD.

The problem I am facing is:
If the command button to open the UserForm2 is placed on the Sheet2, the code to search all four columns works, but when I placed the command button to call the userform2 from my main userform1, it gives error.

I have replaced terms like CodeA.Value by UserForm2.CodeA.Value etc. and tried the code in a module as well as in UserForm2 but it gives error.

I am not able to properly reference the columns to be searched in sheet2 . eg in the lines SearchColumn = "ColumnCodeA"

I also has issues in the line With Range("Table1[" & SearchColumn & "]")

request help to resolve.
thank you.


VBA Code:
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
    ' codeA... CodeB.....CodeC.... CodeD are textbox names
    'listbox name is Results
    
    If CodeA.Value = "" And CodeB.Value = "" And CodeC.Value = "" And CodeD.Value = "" Then
    
        MsgBox "No search term specified", vbCritical + vbOKOnly
        Exit Sub
    
    End If
    
    ' Work out what is being searched for
    If CodeA.Value <> "" Then
    
        SearchTerm = CodeA.Value
        SearchColumn = "ColumnCodeA"
        
    End If
    
    If CodeB.Value <> "" Then
    
        SearchTerm = CodeB.Value
        SearchColumn = "ColumnCodeB"
        
    End If

    If CodeC.Value <> "" Then
    
        SearchTerm = CodeC.Value
        SearchColumn = "ColumnCodeC"
        
    End If

    If CodeD.Value <> "" Then
    
        SearchTerm = CodeD.Value
        SearchColumn = "ColumnCodeD"
        
    End If
    
    Results.Clear
    
        ' Only search in the relevant table column i.e. if somone is searching in text box CodeA
        ' only search in the  ColumnCodeA
        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, 4)
                    Results.List(RowCount, 3) = 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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
Seems like U need to make these changes. HTH. Dave
Code:
With Sheets("Sheet2").Range("Table1[" & SearchColumn & "]")
Set FirstCell = Sheets("Sheet2").Range("A" & RecordRange.Row)
 

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
thanks for
Seems like U need to make these changes. HTH. Dave
Code:
With Sheets("Sheet2").Range("Table1[" & SearchColumn & "]")
Set FirstCell = Sheets("Sheet2").Range("A" & RecordRange.Row)
Thanks for the suggestion.

The code gives error 1004 - application defined or object defined error at line

With Sheets("Sheet2").Range("Table1[" & SearchColumn & "]")


Should I place this code in the userform or in a module?


regards.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
My suggestion was to specify your sheet in case the active sheet was not sheet 2. You should also change and qualify all of these userform textbox references (SearchTerm is declared as a string)...
Code:
SearchTerm = Userform2.CodeA.Text
You can place the code in module code accessible to the whole project by changing "private sub" to "public sub" which will allow you to call the macro from either userform. As far as your code syntax error, you will need to figure out how to properly refer to your table column. Here's a link that might help. HTH. Dave

 

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

My suggestion was to specify your sheet in case the active sheet was not sheet 2. You should also change and qualify all of these userform textbox references (SearchTerm is declared as a string)...
Code:
SearchTerm = Userform2.CodeA.Text
You can place the code in module code accessible to the whole project by changing "private sub" to "public sub" which will allow you to call the macro from either userform. As far as your code syntax error, you will need to figure out how to properly refer to your table column. Here's a link that might help. HTH. Dave

I tried to modify the code till i got no break point in the code, but the listbox is not populating with the desired values/ column data after search.

I have placed the code in a module now.

I want to populate data from column A, B, D and G from sheet named "Receipts" in ListBox1 .
There are 4 search boxes to search from these four columns. - Textbox1,2,3 and 4.

can somebody help me in this further.


VBA Code:
Sub Search()

    Dim SearchTerm As String
    Dim SearchColumn As String
    Dim RecordRange As Range
    Dim FirstAddress As String
    Dim FirstCell As Range
    Dim RowCount As Integer

 

    ThisWorkbook.Sheets("Receipts").Activate

        With UserForm8



    ' Validation of data entry
    If UserForm8.TextBox1.Value = "" And UserForm8.TextBox2.Value = "" And UserForm8.TextBox3.Value = "" And UserForm8.TextBox4.Value = "" Then

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

    End If

    ' Details for search text / column
    If UserForm8.TextBox1.Value <> "" Then

       UserForm8.TextBox1.Text = SearchTerm
        Sheets("Receipts").Columns.Item("A") = SearchColumn


    End If

    If UserForm8.TextBox2.Value <> "" Then

        UserForm8.TextBox2.Text = SearchTerm
        Sheets("Receipts").Columns.Item("B") = SearchColumn


    End If

    If UserForm8.TextBox3.Value <> "" Then

        UserForm8.TextBox3.Text = SearchTerm
        Sheets("Receipts").Columns.Item("D") = SearchColumn
                
    End If

    If UserForm8.TextBox4.Value <> "" Then

       UserForm8.TextBox4.Text = SearchTerm
       Sheets("Receipts").Columns.Item("G") = SearchColumn


    End If

    UserForm8.ListBox1.Clear

        ' Only search in the relevant table column i.e. 

With Sheets("Receipts").Range("Table" & SearchColumn)

'With Sheet2
            ' 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
                    UserForm8.ListBox1.AddItem
                    UserForm8.ListBox1.List(RowCount, 0) = FirstCell(1, 1)
                    UserForm8.ListBox1.List(RowCount, 1) = FirstCell(1, 2)
                    UserForm8.ListBox1.List(RowCount, 2) = FirstCell(1, 4)
                    UserForm8.ListBox1.List(RowCount, 3) = FirstCell(1, 7)
                    UserForm8.ListBox1.List(RowCount, 4) = FirstCell(1, 9)
                    UserForm8.ListBox1.List(RowCount, 5) = FirstCell(1, 10)
                    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 no matches were found
                UserForm8.ListBox1.AddItem
                UserForm8.ListBox1.List(RowCount, 0) = "Nothing Found"

            End If

        End With
        
         End With
        
         End With


End Sub
 

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
while i was trying this code, i found that the contents of the columns A,B,D and G in the sheet got deleted, i dont know how the code deleted the contents.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
As far as I can tell, U haven't specified what SearchColumn is (or SearchTerm)??? So if SearchColumn is blank, setting the contents of A,B,D and G equal to SearchColumn will make them blank (not deleted). HTH. Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,129,810
Messages
5,638,483
Members
417,027
Latest member
wlknspc7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top