Populate UserForm based off TextBox value

thechad

Board Regular
Joined
Apr 28, 2014
Messages
118
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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:

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
 
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.
 
Upvote 1

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,223,883
Messages
6,175,168
Members
452,615
Latest member
bogeys2birdies

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
Back
Top