User Form Search

[SoyuzGRU]

Background

I am looking to automate excel and have an excel that includes 3 sheets:

Master Sheet (Sheet1) - All information from user forms are stored here (sheet will be locked)
Data Validation (Sheet2) - Information from user form combo boxes are here (sheet will be locked)
Menu (Sheet3) - User interface with buttons that open user forms

Assistance

I am trying to setup a user form where a user can view records in the Master sheet through the input of up to 3 text box fields (All labelled by their default names "TextBox1", etc.). Once the user hits submit, I'd like the form to compile all rows of data that matches the search criteria to a List Box at the bottom of the form.

On the more complicated side, the Master sheet has about 11 columns of data in one row. I want all this data displayed, but the user is only filtering through 3 columns as followed:

User Form TextBox1 - Searches Master Column D
User Form TextBox2 - Searches Master Column E
User Form TextBox3 - Searches Master Column F

I've looked at countless tutorials, and tried a few codes found in them but can't seem to get the results desired. While I am a novice to VBA I'd appreciate a short explanation if possible on any codes provided.

As always, I appreciate any help

 

[DanteAmor]

You don't need a button to do the search.
With the following code, just start typing in any of the 3 textboxes and the search is done automatically.
You can enter one or several letters, the code looks for similar data, not necessarily the entire match.

Fit your sheet data in the code.

Dim a As Variant    'At the beginning of all the code

Private Sub TextBox1_Change()
  Call FilterData
End Sub
Private Sub TextBox2_Change()
  Call FilterData
End Sub
Private Sub TextBox3_Change()
  Call FilterData
End Sub

Sub FilterData()
  Dim txt1 As String, txt2 As String, txt3 As String
  Dim b As Variant
  Dim i As Long, j As Long, k As Long
 
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  ListBox1.Clear
  For i = 1 To UBound(a, 1)
    If TextBox1.Value = "" Then txt1 = a(i, 4) Else txt1 = TextBox1.Value
    If TextBox2.Value = "" Then txt2 = a(i, 5) Else txt2 = TextBox2.Value
    If TextBox3.Value = "" Then txt3 = a(i, 6) Else txt3 = TextBox3.Value
    If LCase(a(i, 4)) Like "*" & LCase(txt1) & "*" And LCase(a(i, 5)) Like "*" & LCase(txt2) & "*" _
       And LCase(a(i, 6)) Like "*" & LCase(txt3) & "*" Then
      j = j + 1
      For k = 1 To UBound(a, 2)
        b(j, k) = a(i, k)
      Next
    End If
  Next i
  If j > 0 Then ListBox1.List = b
End Sub

Private Sub userform_initialize()
  'Fit column "K" to the last column with data
  'Fit cell "A2" with the starting cell of data
  a = Sheets("Sheet1").Range("A2:K" & Sheets("Sheet1").Range("D" & Rows.Count).End(3)).Value
  ListBox1.ColumnCount = UBound(a, 2)
End Sub

 

[SoyuzGRU]

Thanks for the reply and the help.

I copied the code provided and input into the excel however upon opening the form and inputting anything into either of the text boxes I get diverted to the code and I have highlighted in yellow:

Sub FilterData()

As well as the following highlighted in red:

ReDim b(1 To UBound(a,1),1 To UBound(a,2))

Any further help?

 

[DanteAmor]

You must delete all the code you have in your userform.
Full copy my code.
If you modified the code, then post the new code here for me to review.

 

[SoyuzGRU]

You must delete all the code you have in your userform.
Full copy my code.
If you modified the code, then post the new code here for me to review.

I've copied the code exactly as you provided and when I ran the userform I received a pop up with a runtime error 9.

Apart from your code I have the following on the same userform, which is input above the code provided:

Private Sub TextBox3_Exit(ByVal Cancel As MsForms.ReturnBoolean)
TextBox3.Text = Format$(TextBox3.Text, "000 000 000")
End Sub

Thanks

 

[DanteAmor]

runtime error 9.

In addition to the error message, you can hit the debug button, check which line of the macro is highlighted in yellow, and come back here and type here which line is highlighted in yellow.
The error sounds like your sheet name is different from my example:

Private Sub userform_initialize()
  'Fit column "K" to the last column with data
  'Fit cell "A2" with the starting cell of data
  a = Sheets("Sheet1").Range("A2:K" & Sheets("Sheet1").Range("D" & Rows.Count).End(3)).Value
  ListBox1.ColumnCount = UBound(a, 2)
End Sub

 

[SoyuzGRU]

In addition to the error message, you can hit the debug button, check which line of the macro is highlighted in yellow, and come back here and type here which line is highlighted in yellow.
The error sounds like your sheet name is different from my example:

Private Sub userform_initialize()
  'Fit column "K" to the last column with data
  'Fit cell "A2" with the starting cell of data
  a = Sheets("Sheet1").Range("A2:K" & Sheets("Sheet1").Range("D" & Rows.Count).End(3)).Value
  ListBox1.ColumnCount = UBound(a, 2)
End Sub

The name was partially it, I had a look at the line you posted and Sheet1 for me is named "Master" which I had changed in the coding. Following that change trying to run the User Form I now receive a 438 error, however, this does not give me the option to debug and see the highlighted field of code

** Sorry correction - Once I run the User Form through my Menu page the debug option shows up and the button that opens up the User Form seems to have that error too

Private Sub CommandButton1_Click()
UserForm2.Show
End Sub

 

[DanteAmor]

What does the error message say?
Your userform is called userform2?

You could skip that button for a moment, go directly to the userform and run it with F5, that way we'll review the functionality of the code I sent you.

 

[SoyuzGRU]

What does the error message say?
Your userform is called userform2?

You could skip that button for a moment, go directly to the userform and run it with F5, that way we'll review the functionality of the code I sent you.

I'm not 100% sure how to complete that, I was hoping to upload a copy of the excel to share with you so you can see the extent of it, however wasn't able to. The following are a few images of the excel.

It seems the error im receiving is related to the main menu button the opens the user form.

Sorry

 

[DanteAmor]

You have problems, but it's not with my code.
Nowhere in my code do I have those instructions that you put in the image.

That's why I wrote to you, forget your code and try my code.

If you want me to review your code, you'll need to share your file.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.