Search & Display Userform Not Returning Text and Error Code

[nekiwa07]

Hi,

I'm attempting to create a userform that allows search and display. I've used the below code in the past and have worked for all worksheets w/I a workbook for some reason won't work for this one, please help. When it was included in the large workbook, I did not receive an error message BUT when I enter the "Last Name" in the first text box of the form, it would not render any values in the remaining boxes but remained blank.

I decided to copy this worksheet to create a new book with only this one sheet included and recreated the form and code and now I'm getting an error message "Object not found".

Can someone please help? I don't think there is anything wrong with the code because works with other sheets is there something wrong with this sheet? I've also attached a copy of the workbook and macro

Private Sub CommandButton1_Click()

Dim acct_no_1 As String
acct_no_1 = Trim(TextBox1.Text)
Lastrow = Worksheets("GOVs & Operators").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To Lastrow
If Worksheets("GOVs & Operators").Cells(i, 1).Value = last_name Then
TextBox2.Text = Worksheets("GOVs & Operators").Cells(i, 2).Value
TextBox3.Text = Worksheets("GOVs & Operators").Cells(i, 3).Value
TextBox4.Text = Worksheets("GOVs & Operators").Cells(i, 4).Value
TextBox5.Text = Worksheets("GOVs & Operators").Cells(i, 5).Value
TextBox6.Text = Worksheets("GOVs & Operators").Cells(i, 6).Value
End If
Next

End Sub

[AlphaFrog]

Hi and welcome to the forum. There is quite a bit wrong with the code. It couldn't possibly work as it is.

1.) Your example workbook userform has no textboxes. The code refers to six different textboxes.

2.) You enter the account number in TextBox1, but you try to match the last name. The code has no defined last_name.

Please Login or Register  to view this content.

[nekiwa07]

I've updated the code see below and attached. I'm now getting a "run time error 9" saying "subscript error" can you please help?

Private Sub SearchDisplay_Click()

Dim acct_no_1 As String
acct_no_1 = Trim(TextBox1.Text)
Lastrow = Worksheets("GOVs & Operators").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To Lastrow
If Worksheets("GOVs & Operators)").Cells(i, 1).Value = acct_no_1 Then
TextBox2.Text = Worksheets("GOVs & Operators").Cells(i, 2).Value
TextBox3.Text = Worksheets("GOVs & Operators").Cells(i, 3).Value
TextBox4.Text = Worksheets("GOVs & Operators").Cells(i, 4).Value
TextBox5.Text = Worksheets("GOVs & Operators").Cells(i, 5).Value
TextBox6.Text = Worksheets("GOVs & Operators").Cells(i, 6).Value
End If
Next

End Sub