Vba userform

[Ed_Filipino]

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.

[TABLE="width: 584"]
<tbody>[TR]
[TD]CODE[/TD]
[TD]MAKE[/TD]
[TD]MODEL[/TD]
[TD]SERIAL[/TD]
[TD]DELIVERY DATE[/TD]
[/TR]
[TR]
[TD]TR1[/TD]
[TD]DAF[/TD]
[TD]CF75 EURO 3[/TD]
[TD]XLRAT75PCOE555083[/TD]
[TD]
20-Mar-2000

[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

 

[My Aswer Is This]

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

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]

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:

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

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]

Another option

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.

 

[Ed_Filipino]

Thank you for your reply...