Populate UserForm based off TextBox value

T

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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Hi @thechad. Thanks for posting on MrExcel board.

Please try the following code. Adjust the corresponding columns to fill each textbox.

VBA Code: 
Private Sub CommandButton1_Click()
  Dim sh1 As Worksheet
  Dim f As Range
  
  Set sh1 = Sheets("Sheet1")
  
  With TextBox1
    If .Value = "" Then
      MsgBox "Enter textbox1"
      Exit Sub
    End If
    
    Set f = Sheets("Sheet2").Range("B:B").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      MsgBox "that it already exists"
      Set f = sh1.Range("E:E").Find(.Value, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        TextBox2.Value = sh1.Range("A" & f.Row).Value
        TextBox3.Value = sh1.Range("B" & f.Row).Value
        TextBox4.Value = sh1.Range("c" & f.Row).Value
      Else
        MsgBox "does not exist on sheet1"
      End If
    Else
      MsgBox "Not exists on sheet2"
    End If
  End With
End Sub


--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------
 
Upvote 0
DanteAmor said:
Hi @thechad. Thanks for posting on MrExcel board.

Please try the following code. Adjust the corresponding columns to fill each textbox.

VBA Code: 
Private Sub CommandButton1_Click()
  Dim sh1 As Worksheet
  Dim f As Range
 
  Set sh1 = Sheets("Sheet1")
 
  With TextBox1
    If .Value = "" Then
      MsgBox "Enter textbox1"
      Exit Sub
    End If
   
    Set f = Sheets("Sheet2").Range("B:B").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      MsgBox "that it already exists"
      Set f = sh1.Range("E:E").Find(.Value, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        TextBox2.Value = sh1.Range("A" & f.Row).Value
        TextBox3.Value = sh1.Range("B" & f.Row).Value
        TextBox4.Value = sh1.Range("c" & f.Row).Value
      Else
        MsgBox "does not exist on sheet1"
      End If
    Else
      MsgBox "Not exists on sheet2"
    End If
  End With
End Sub


--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------
Click to expand...
Hi Dante,

Here are some of my ongoing issues:

If the value in TextBox1 shows up in Sheets("Sheet2").Range("B:B"), the msgbox pops up with the proper warning however, once the "OK" button is clicked the macro continues on, however it should end once "OK" is selected.

If the value in TextBox1 does not show up in Sheets("Sheet2").Range("B:B") the macro goes straight to msgbox "Not exists on sheet2" however I know the TextBox1 value I entered exists on Sheet2 in range E:E so it seems to be missing it. None of the other TextBoxes populate at any time either.

Thanks,
Chad
 
Upvote 0
thechad said:
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
Click to expand...
It's my fault, I didn't quite understand that part.

Try now:
VBA Code: 
Private Sub CommandButton1_Click()
  Dim sh1 As Worksheet
  Dim f As Range
  
  Set sh1 = Sheets("Sheet1")
  
  With TextBox1
    If .Value = "" Then
      MsgBox "Enter textbox1"
      Exit Sub
    End If
    
    '1. Search "Sheet2", Column B2:B200000
    Set f = Sheets("Sheet2").Range("B:B").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      MsgBox "that it already exists"
      Exit Sub
    End If
    
    '2.Search "Sheet1", Column E2:E200000
    Set f = sh1.Range("E:E").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      TextBox2.Value = sh1.Range("A" & f.Row).Value
      TextBox3.Value = sh1.Range("B" & f.Row).Value
      TextBox4.Value = sh1.Range("c" & f.Row).Value
    Else
      MsgBox "does not exist on sheet1"
    End If
  End With
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
 
Upvote 0
DanteAmor said:
It's my fault, I didn't quite understand that part.

Try now:
VBA Code: 
Private Sub CommandButton1_Click()
  Dim sh1 As Worksheet
  Dim f As Range
 
  Set sh1 = Sheets("Sheet1")
 
  With TextBox1
    If .Value = "" Then
      MsgBox "Enter textbox1"
      Exit Sub
    End If
   
    '1. Search "Sheet2", Column B2:B200000
    Set f = Sheets("Sheet2").Range("B:B").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      MsgBox "that it already exists"
      Exit Sub
    End If
   
    '2.Search "Sheet1", Column E2:E200000
    Set f = sh1.Range("E:E").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      TextBox2.Value = sh1.Range("A" & f.Row).Value
      TextBox3.Value = sh1.Range("B" & f.Row).Value
      TextBox4.Value = sh1.Range("c" & f.Row).Value
    Else
      MsgBox "does not exist on sheet1"
    End If
  End With
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
Click to expand...

No it really is my fault. It's sometimes difficult to articulate what is desired without seeing everything or knowing how to explain it properly. I applaud folks like you that can read between the lines and at least get me close to what I need and we just make some tweaks from that point.

Still an ongoing issue though...

If TextBox1 is not blank, does not match a value in "Sheet2", but definitely matches a value in "Sheet1", I get an Run-time error '424' and it highlights this part of the code in yellow:
Badge.Value = shl.Range("D" & f.Row).Value

For context, that is part of this section in the code:
VBA Code: 
    '2.Search "Sheet1", Column E2:E200000
    Set f = sh1.Range("E2:E200000").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
        Badge.Value = shl.Range("D" & f.Row).Value
        Region.Value = shl.Range("A" & f.Row).Value
        District.Value = shl.Range("B" & f.Row).Value
        OffenceDate.Value = shl.Range("C" & f.Row).Value
        ChargeType.Value = shl.Range("F" & f.Row).Value
        Legislation.Value = shl.Range("H" & f.Row).Value
        Section.Value = shl.Range("I" & f.Row).Value
        Defendant.Value = shl.Range("G" & f.Row).Value
    Else
      MsgBox "does not exist on sheet1"
    End If
  End With
 
Upvote 0
DanteAmor said:
The error is because Badge does not exist as a control, check that it is well written.
Click to expand...

Badge is one of my TextBoxes that replaced TextBox2.Value. I just renamed TextBox2 to Badge. Is there something else I need to do?
 
Upvote 0
You are not doing something right, you should check the names. You can put the name you want, textbox2 or badge, but in the code you must write them exactly the same.

It's a shame, but you don't put the complete information, you don't put your complete macro, you should put an image where we can appreciate the name of your textbox.
Example:

1683575149881.png



Or share your file to review it.

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.
 
Upvote 0
Sorry...not trying to be difficult. Here is the full code:
VBA Code: 
Private Sub checkmatter_Click()

  Dim sh1 As Worksheet
  Dim f As Range
  Dim ws As Worksheet
  Dim Rng As Range
  Dim answer As Integer
  
  Set sh1 = Sheets("CHARGE DATA")
  
  With PON
    If .Value = "" Then
      MsgBox "Enter a PON number to determine if it already exists within the Court Tracker or if it has been entered through the ePON system."
      Exit Sub
    End If
    
    '1. Search "Sheet2", Column B2:B200000
    Set f = Sheets("MAIN").Range("B6:B200000").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then GoTo 1
    
    '2.Search "Sheet1", Column E2:E200000
    Set f = sh1.Range("E2:E200000").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
        Badge.Value = shl.Range("D" & f.Row).Value
        Region.Value = shl.Range("A" & f.Row).Value
        District.Value = shl.Range("B" & f.Row).Value
        OffenceDate.Value = shl.Range("C" & f.Row).Value
        ChargeType.Value = shl.Range("F" & f.Row).Value
        Legislation.Value = shl.Range("H" & f.Row).Value
        Section.Value = shl.Range("I" & f.Row).Value
        Defendant.Value = shl.Range("G" & f.Row).Value
    Else
      MsgBox "This PON was not entered through the ePON system and must be entered manually"
    End If
  End With
  
1:
    answer = MsgBox("That matter already exists within the Court Tracker. Would you like to go there now?", vbQuestion + vbYesNo + vbDefaultButton2, "MATTER ALREADY EXISTS")
    If answer = vbYes Then
    Set ws = Sheets("MAIN")   'Change the sheet name as per requirement
    Set Rng = ws.Range("B6:B200000").Find(Me.PON.Value)
    If Not Rng Is Nothing Then
        ws.Activate 'this line makes sure that you are on the right sheet
        Rng.Select
    End If
    Unload Me
    Exit Sub
    
    Else
        Exit Sub
    End If
    

End Sub

And here is a screenshot of the TextBox & corresponding name specifically for Badge

1683575986742.png


I can't share the workbook unfortunately due to the nature of the content.
 
Upvote 0
The sh1 object has the number 1 (one)
thechad said:
Set sh1 = Sheets("CHARGE DATA")
Click to expand...
thechad said:
Set f = sh1.Range("E2:E200000").Find(.Value, , xlValues, xlWhole, , , False)
Click to expand...

And on this line you put the letter l
thechad said:
Badge.Value = shl.Range("D" & f.Row).Value
Click to expand...

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).
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

C
  • Question Question
Force user to enter at least one symbol and one number in a textbox through a userform
Replies
9
Views
297
christianbiker
C
C
  • Question Question
Populate textboxes based on row number
Replies
4
Views
249
christianbiker
C
hendrikbez
  • Question Question
Cannot get to edit on my user form with 1 to 5 members on it
Replies
1
Views
257
Anthony47
A
I
  • Question Question
Userform does not show leading 0 for telephone number
Replies
1
Views
258
ipbr21054
I
I
  • Solved
Load into textbox two values from customer on worksheet
Replies
6
Views
74
AlphaFrog
AlphaFrog

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

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