Populate UserForm based off TextBox value

[thechad]

Greetings all,

I am trying to populate TextBoxes within a UserForm from a worksheet based off user inputted data within a specific TextBox named PON in the same UserForm. The other TextBoxes would need to show corresponding values from the same row from columns 2, 3, 4, etc.

For example...if a user enters 258954Z into TextBox "PON", I would like to have the following functionality unsing a CommandButton within the UserForm:

1. Search "Sheet2", Column B2:B200000 to see if the data entered in the PON textbox already exists and, if it does, provide a msgbox popup that indicates that it already exists and allow for the user to exit the macro with an OK button
2. Search "Sheet1", Column E2:E200000 to see if the data entered in the PON textbox already exists, and if so, populate the other TextBoxes using corresponding values from the same row from columns 2, 3, 4, etc.
   • If the data does not exist in "Sheet1", Column E2:E200000, display a msgbox popup that indicates it does not exist and that it needs to be entered manually with an OK button to exit macro

DanteAmor was kind enough to help start the process with the following VBA code:

Private Sub ComboBox1_Change()
  Dim f As Range
 
  If ComboBox1.ListIndex = -1 Or ComboBox1.Value = "" Then
    Exit Sub
  End If

  With Sheets("Sheet1")
    'column 1 (A) on worksheet "Sheet1"
    Set f = .Range("A:A").Find(ComboBox1.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      'I need the textboxes to show corresponding values from the same row from columns 2, 3, 4, etc.
      TextBox1.Value = .Range("B" & f.Row).Value
      TextBox2.Value = .Range("C" & f.Row).Value
      TextBox3.Value = .Range("D" & f.Row).Value
      TextBox4.Value = .Range("E" & f.Row).Value
      TextBox5.Value = .Range("F" & f.Row).Value
    End If
  End With
End Sub

Any help as always is greatly appreciated.

Thanks,
Chad

 

[thechad]

The sh1 object has the number 1 (one)



And on this line you put the letter l


Correct that data, everything should work fine.


Tip: At the beginning of all your code put the Option Explicit statement, this will help you verify that all variables are declared (among other things).

Thanks for all your help AND more importantly, patience with me Dante. Not sure how I even did that but totally my bad.