Getting the userform to import data from an excel sheet

[neilybig]

Hi all,
I have an issue that I hope you can help me with.

I have a userform that users fill in to complete, this then emails a notification and populates an excel sheet. This works well, however I need a different userform to pull a row of data. My thoughts would be a combobox that looks for the row reference (A2 to AXXX) this would then allow the rest of the userform to pull the correct data into it. TextBox1 (Column C), TextBox2 (Column F).

e.g. ID 4 (row 5), so Textbox1 (Column C, Row 5) TextBox2 (Column F, Row 5)

The second component would be for new data to be added to this userform and this is added to the same line but different columns to the data pulled in.

Any help or suggestions appreciated

Neil

 

[Kamolga]

e.g. ID 4 (row 5), so Textbox1 (Column C, Row 5) TextBox2 (Column F, Row 5)

Hi,

I would use something like this (untested)

Private Sub UserForm_Activate()
Dim sht As Worksheet
sht = thisworkbook.Activesheet
Dim x As Long
    For x = 2 To Cells(sht.Rows.Count, "A").End(xlUp).Row
        If sht.Range("A" & x).Value = [COLOR=#0000ff]"ID 4"[/COLOR] Then
           Textbox1.Value = sht.Range("C" & x)
           TextBox2.Value = sht.Range("F" & x)
        End If
    Next x
End Sub

and find a way to get the value ID 4 dynamically into the if condition

 

[neilybig]

Hi,
Thanks for the feedback, I have adjusted the above and have this
Private Sub ComboBox1_Change()
Dim lbtarget As MSForms.ComboBox
Dim i As Long, Lastrow As Long
Lastrow = Sheets("Sheet6").Range("A" & Rows.Count).End(x1up).Row
For i = 2 To Lastrow
If Sheets ("Sheet6").Cells(i, "A").Value = (me.Combobox1) Or _
Sheets ("Sheet6").Cells(i, "A").Value = Val((Me.Combobox1) Then
Me.TextBox1 = Sheets("Sheet6").Cells(i = "B").Value
Me.TextBox2 = Sheets("Sheet6").Cells(i = "F").Value
Next i
End If
End Sub

Any idea why I might get a syntax error?

Any help much appreciated
Neil

 

[My Aswer Is This]

Try this:

Me.TextBox1 = Sheets("Sheet6").Cells(i, "B").Value
Me.TextBox2 = Sheets("Sheet6").Cells(i, "F").Value

I replaced = with ,

 

[neilybig]

Update:-
I have fixed simply with the code below

Private Sub ComboBox1_Change()
TextBox1.Value = Application.WorksheetFunction.VLookup(Val(ComboBox1.Value), Sheets("Trial Tracker").Range("a2:M100"), 10, 1)
TextBox5.Value = Application.WorksheetFunction.VLookup(Val(ComboBox1.Value), Sheets("Trial Tracker").Range("a2:M100"), 12, 1)
TextBox2.Value = Application.WorksheetFunction.VLookup(Val(ComboBox1.Value), Sheets("Trial Tracker").Range("a2:M100"), 13, 1)
End Sub