Copy list of values from textbox in UserForm into Excel sheet

Question

I have a UserForm (frm10) with two text boxes (txt11 and txt12). Both text boxes contain a list of values.

Is there a way I can copy the values from these boxes in the underlying sheet (Sheet1) so that each value appears in a separate cell ? I would need all values from txt11 appear in column A of my sheet and all values from txt12 in column B of the same sheet.

Many thanks for any help with this, Tim.

Answer 1

It depends on your delimiter but something like the below would work, with a little tidy up (I haven't worked with UserForms for long time but the approach will be the same), you'll just need to adapt to handle both, change destination start etc:

Edited so you can set the delimiter

'If you value list is separated by comma
Dim x As Integer, y As Integer
Dim str1 as String, strDelim as String
Dim sh As Worksheet
Dim c As Range

str1 = txt11 '(can't recall how you allocate from userform to variable...)
strDelim = Chr(10) ' chr(10) is new line feed, you may need to test what the 
    ' delimiter is, it could be chr(13) (carriage return)

' Add trailing comma so captures last value
If Right(str1, 1) <> strDelim  Then
    str1 = str1 & strDelim 
End If

' Set sheet and destination for values to start
Set sh = Worksheets("Sheet1")
Set c = sh.Range("A1")

' Set initial x and y
x = InStr(str1, strDelim)
y = 1

' Check at least 1 comma and that value was entered
If x = 0 Then
    If Len(str1) > 0 Then
        c.Value = str1
    End If
End If

' Loop through commas, paste value in cell and offset destination
Do Until x = 0
    c.Value = Mid(str1, y, x - y)
    y = x + 1
    x = InStr(y, str1, strDelim)
    Set c = c.Offset(1, 0)
Loop

Set c = Nothing
Set sh = Nothing

If the delimiter depends on how they enter a new line then you could replace so they are all the same

str1 = Replace(str1,chr(13),chr(10))

And then use chr(10) as the delimeter