Copy cells from column to userform text boxes by selection

[pjbassdc]

Hello all,

I have a worksheet with some data I would like to transfer to a userform. The form has a combobox that a testing modality can be selected. I want to know how to get the remainder of the userform text boxes to populate with the proper column information from the worksheet. Thank you for all your help.


Patrick

[Tinbendr]

Private Sub ComboBox1_Change()
Dim WS As Worksheet
Dim Rng As Range
Dim A As Long
Dim Temp As String

Set WS = Worksheets("Sheet1")

With WS.Range("C1:G1")
    Set Rng = .Find(Me.ComboBox1, , xlValues)
    If Not Rng Is Nothing Then
        For A = 1 To 6
            Me.Controls("TextBox" & A) = .Cells(A + 1, Rng.Column)
        Next
        For A = 8 To 12
            Temp = Temp & .Cells(A, Rng.Column) & vbCr
        Next
        Me.TextBox7.MultiLine = True
        Me.TextBox7 = Temp
    End If
End With
End Sub

[pjbassdc]

Thank you for the code David. I tried it out and it works, sort of. Not sure why, but it seems to index 2 columns to the right and pulls in the information from the wrong column. Example: I make my selection in the combobox for what should pull, column C. Instead of column C, I get info from coulumn E. I haven't seen anywhere in the code that jumps out and says hey go 2 to the right. My data range is in columns C thru G at this time. Any ideas on what I should do to correct this?

Thanks,
Patrick

[pjbassdc]

Thank you for the code David. I tried it out and it works, sort of. Not sure why, but it seems to index 2 columns to the right and pulls in the information from the wrong column. Example: I make my selection in the combobox for what should pull, column C. Instead of column C, I get info from coulumn E. I haven't seen anywhere in the code that jumps out and says hey go 2 to the right. My data range is in columns C thru G at this time. Any ideas on what I should do to correct this?

Thanks,
Patrick

[pjbassdc]

Spent some time working with the code. I made a small edit and it seems to have fixed the issue. I changed the range from C1:G1 to A1:G1. Here is the edited code:

Private Sub ComboBox1_Change()
Dim WS As Worksheet
Dim Rng As Range
Dim A As Long
Dim Temp As String

Set WS = Worksheets("Sheet1")

With WS.Range("A1:G1")
    Set Rng = .Find(Me.ComboBox1, , xlValues)
    If Not Rng Is Nothing Then
        For A = 1 To 6
            Me.Controls("TextBox" & A) = .Cells(A + 1, Rng.Column)
        Next
        For A = 8 To 12
            Temp = Temp & .Cells(A, Rng.Column) & vbCr
        Next
        Me.TextBox7.MultiLine = True
        Me.TextBox7 = Temp
    End If
End With
End Sub

[Tinbendr]

Glad you got it sorted. I was on the road today, hence my silence. Great work!