Ed_Filipino

New Member
Joined
Jan 5, 2018
Messages
6
HI, Im a newbie here I just want to ask help because I have hard time finishing my VBA userform. I have a 5 column table I a excele worksheet. I want to have a userform that have 5 textbox according to the headings of the 5 column, im planning that in the 1st textbox if I entry the data the 4 remaining columns will auto fill themselves according to their respective row in worksheet: Example of the table shown below. So im planning if I type the CODE in the first textbox the 4 columns pertaining to it will auto fill the other textbox.

Im hoping you can help me guys because im trying to make this for two days and it doesn't work for me. Im having trouble more on generating macro cod.

CODEMAKEMODELSERIALDELIVERY DATE
TR1DAFCF75 EURO 3XLRAT75PCOE555083
20-Mar-2000


<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
Well assuming your Textboxes are named.
Textbox1 and Textbox2 and so on these are the default names.

And the value entered into Textbox1 will be found in the active sheet column "A"
The search begins in row two of Column "A"
If no value is found you will get a Message Box saying no value found

You can try this script.

You will need to put this script into Textbox1
The script will run when you exit out of Textbox1


Code:
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo M
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim SearchString As String
Dim SearchRange As Range
SearchString = TextBox1.Value
Set SearchRange = Range("A2:A" & Lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
TextBox2.Value = SearchRange.Offset(0, 1).Value
TextBox3.Value = SearchRange.Offset(0, 2).Value
TextBox4.Value = SearchRange.Offset(0, 3).Value
TextBox5.Value = SearchRange.Offset(0, 4).Value
Exit Sub
M:
MsgBox "The value " & TextBox1.Value & "  Was not found"
End Sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
Hi welcome to forum.

If you want record to appear as you enter text in 1st Textbox then maybe following will do what you want:

Rich (BB code):
Private Sub TextBox1_Change()
    Dim Search As String
    Dim FoundCell As Range
    Dim c As Integer
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Search = TextBox1.Value
    Set FoundCell = ws.Columns(1).Find(Search, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    
    For c = 2 To 5
        With Me.Controls("TextBox" & c)
           If FoundCell Is Nothing Then .Text = "" Else .Text = ws.Cells(FoundCell.Row, c).Text
        End With
    Next c
End Sub

Code assumes that your textboxes have their default names
Change the sheet name shown in RED as required.

Hope Helpful

Dave
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
Well assuming your Textboxes are named.
Textbox1 and Textbox2 and so on these are the default names.
And the value entered into Textbox1 will be found in the active sheet column "A"
The search begins in row two of Column "A"

You can try this script.

You will need to put this script into Textbox1
The script will run when you enter value in TextBox1

Code:
Private Sub TextBox1_Change()
Dim Lastrow As Long
Dim SearchString As String
Dim SearchRange As Range
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
SearchString = TextBox1.Value
Set SearchRange = Range("A2:A" & Lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then Exit Sub
TextBox2.Value = SearchRange.Offset(0, 1).Value
TextBox3.Value = SearchRange.Offset(0, 2).Value
TextBox4.Value = SearchRange.Offset(0, 3).Value
TextBox5.Value = SearchRange.Offset(0, 4).Value
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
Another option
Code:
Option Explicit
[COLOR=#0000ff]Dim Dic As Object[/COLOR]

Private Sub Textbox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Dim Cnt As Long
   For Cnt = 2 To 5
      Me.Controls("TextBox" & Cnt).Value = Dic(TextBox1.Value)(1, Cnt - 1)
   Next Cnt
End Sub

Private Sub UserForm_Initialize()
   Dim Cl As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.comparemode = vbTextCompare
   With Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Not Dic.exists(Cl.Value) Then Dic.Add Cl.Value, Cl.Offset(, 1).Resize(, 4).Value
      Next Cl
   End With
End Sub
Change sheet name in red if needed. The Dim statement in blue must go at the top of the module, before any code.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

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
Top