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:
Any help as always is greatly appreciated.
Thanks,
Chad
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:
- 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
- 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
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