Need a way to paste spreadsheet data into my vba form cells?

Question

Need a way to paste spreadsheet data into my vba form cells?

I have a csv spreadsheet updated with new data that I download every day. I want to copy all columns in this csv file and paste it into my vba form in another workbook in their corresponding textboxes.

E.G.

Say the CSV file has 4 columns, Data, ID, Firstname, Surname

I would just copy the whole row with the 4 columns and paste it into my vba form into the correct 4 textboxes.

I don't want to copy and paste each individual, as I have more than 4 columns and I need a faster way so just one paste will paste them into the correct textboxes. Can this be done?

Please help.

Thanks

Answer 1

Private Sub TextBox1_Change()
    Dim splitText
    splitText = Split(Me.TextBox1.Value, vbTab)
    If UBound(splitText) > LBound(splitText) Then
        Me.TextBox1.Value = splitText(LBound(splitText))
        Me.TextBox2.Value = splitText(LBound(splitText) + 1)
        Me.TextBox3.Value = splitText(LBound(splitText) + 2)
        Me.TextBox4.Value = splitText(LBound(splitText) + 3)
    End If
End Sub

'Now just paste your stuff in the first cell

Answer 2

Inspired by Dirk's solution, I've added some features to the make the form dynamic. This is just to show the concept, and it does relate to the original question, specifically to handle a dynamic range of columns from the input. It differs in that the array accounts for a dynamic range of input columns, then creates the text boxes based on the amount of items in the array.

What this does:

• A Command button copies the row (example: "A"), using a lastCol variable for the Range. You can bypass this, as the UserForm will paste the clipboard regardless of it's source, provided you already copied the row.
• User Form with 1 Text Box opens
• The clipboard contents are pasted into Text1 automatically.
• An array is created splitting the value of Text1 by TAB.
• The UserForm is re-sized to fit every text box.
• For each item in the array, (excluding index (0) to be assigned to Text1, a new TextBox is created and the array value assigned.
• Upon closing the UserForm, every TextBox that's name begins with "Text" except Text1 will be removed, resetting the form for the next use.

Notes:

• Using this naming method, the TextBoxes will be named "Text1" - "Text(n)"
• I'm not including labels for the TextBoxes.
• If you have a large amount of columns / TextBoxes, set the property on the UserForm.
  'ScrollBars = 2 fmScrollBarsVertical'

Optional: event to copy the data from Row1 and launch the UserForm, placed in a module.

Sub DynamicTextFormLaunch()
Dim lastCol As Long

    lastCol = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).column

    With ActiveSheet
        .Range("A1", (.Cells(1, lastCol))).Copy
        UserFormName.Show      'Set your UserForm name here
    End With
End Sub

The UserForm code: Everything below here is in the UserForm Code

Sub UserForm_Activate()

    Text1.Paste

End Sub

The text change event:

Sub Text1_Change()
Dim csvArray() As String
Dim lCount As Long, maxCols As Long
Dim tempHeight As String

    csvArray = Split(Text1.Value, vbTab)
    maxCols = UBound(csvArray) + 1
    Text1.Value = csvArray(0)

    tempHeight = 55 + (15.5 * maxCols)    'Set new height for 15.5 pixels per TextBox
    Me.Height = tempHeight
    For lCount = 2 To maxCols             'Not using 1 for Loop because Text1 was already set.

        'Create a new TextBox and assign its size, name, and value.
        Set ctlTXT = Controls.Add("Forms.TextBox.1", "Text" & lCount)   
        ctlTXT.name = "Text" & lCount
        ctlTXT.Left = 15
        ctlTXT.Height = 15: ctlTXT.Width = 100
        ctlTXT.Top = (lCount - 1) * 17 + 2
        ctlTXT.Value = csvArray(lCount - 1)      'Set the value of the new TextBox

    Next lCount
End Sub

The Close Event:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    Call ResetBoxes

End Sub

Remove the Text Boxes that were created.

Private Sub ResetBoxes()

Dim ctrl As Control
Dim tempName As String
Dim tempNum As String

For Each ctrl In Me.Controls
    tempName = Left(ctrl.name, 4)
    tempNum = Right(ctrl.name, 1)

    'Checking to NOT delete Text1
    If tempName = "Text" And tempNum <> "1" Then
        Me.Controls.Remove (ctrl.name)
    End If
Next

    MsgBox ("Removed new TextBoxes, and reset UserForm to original controls.")

End Sub