Pull Worksheet information into Userform

The following code is used on a Userform.

It is used for pulling in information from a Worksheet into a Userform.
For this instance, the workbook will be open, but i don't see why you couldn't reference a closed workbook (by opening it temporarily....)

Firstly, design a userform that looks like this:

 What we have here on the form is:

10 textboxes called TextBox1 through to TextBox10.
1 Listbox called ListBox1
3 Buttons. 
CommandButton1 (top left)
CommandButton2 (bottom left)
CommandButton3 (bottom right)


Once that has been completed, add the following code to the Userform.


Option Explicit
Private Sub CommandButton1_Click()
'Load the ListBox
Dim myRng As Range
Dim cw As Variant
Dim c As Single

With Sheets("Sheet1")
Set myRng = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Offset(, 9))
End With
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
TextBox9.Value = ""
TextBox10.Value = ""

With Me.ListBox1
    .ColumnCount = 10
    .RowSource = myRng.Address(external:=True)
        cw = ""
        For c = 1 To .ColumnCount
        cw = cw & myRng.Columns(c).Width & ";"
        Next c
     .ColumnWidths = cw
     .ListIndex = -1
End With
End Sub


Private Sub CommandButton2_Click()
'Change selection
With ListBox1
If (.Value <> vbNullString) Then
 Range(.RowSource)(.ListIndex + 1, 1).Value = UserForm1.TextBox1.Value
 Range(.RowSource)(.ListIndex + 1, 2).Value = UserForm1.TextBox2.Value
 Range(.RowSource)(.ListIndex + 1, 3).Value = UserForm1.TextBox3.Value
 Range(.RowSource)(.ListIndex + 1, 4).Value = UserForm1.TextBox4.Value
 Range(.RowSource)(.ListIndex + 1, 5).Value = UserForm1.TextBox5.Value
 Range(.RowSource)(.ListIndex + 1, 6).Value = UserForm1.TextBox6.Value
 Range(.RowSource)(.ListIndex + 1, 7).Value = UserForm1.TextBox7.Value
 Range(.RowSource)(.ListIndex + 1, 8).Value = UserForm1.TextBox8.Value
 Range(.RowSource)(.ListIndex + 1, 9).Value = UserForm1.TextBox9.Value
 Range(.RowSource)(.ListIndex + 1, 10).Value = UserForm1.TextBox10.Value
Else
 MsgBox "Please enter data"
End If
End With
End Sub

Private Sub CommandButton3_Click()
Unload Me
End Sub

Private Sub ListBox1_Change()
Dim I
On Error GoTo FTB   '<---- When there are <10 columns
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
TextBox9.Value = ""
TextBox10.Value = ""
FTB:

On Error GoTo ES   '<---- When there are <10 columns
TextBox1.Value = ListBox1.Column(0)
TextBox2.Value = ListBox1.Column(1)
TextBox3.Value = ListBox1.Column(2)
TextBox4.Value = ListBox1.Column(3)
TextBox5.Value = ListBox1.Column(4)
TextBox6.Value = ListBox1.Column(5)
TextBox7.Value = ListBox1.Column(6)
TextBox8.Value = ListBox1.Column(7)
TextBox9.Value = ListBox1.Column(8)
TextBox10.Value = ListBox1.Column(9)
ES:
End Sub

Create a new module and paste the following code. This will launch the Userform.

Sub GetIt()

UserForm1.Show

End Sub