Pasting across multiple userform text boxes

[chrisbelfast]

Hey ive been looking everywhere for a solution and my brain is now wrecked

How can you paste information across multiple userform textboxes? eg

copied info = 06022012

textbox1 maxlength 2, autotab true
textbox2 maxlength 2, autotab true
textbox3 maxlength 4

When textbox1 is selected and you paste it will convert it to the following:

textbox1 value = 06
textbox2 value = 02
textbox3 value = 2012

Additionally can this be done where the input data is across multiple lines? eg

copied info =
1002 this village
this city
this country
post code

textbox1 maxlength 30, autotab true
textbox2 maxlength 30, autotab true
textbox3 maxlength 30, autotab true
textbox4 maxlength 8

Thanks in advance for any help given
Chris

 

[chrisbelfast]

Sorry left out a part:

Additionally can this be done where the input data is across multiple lines? eg

copied info =
1002 this village
this city
this country
post code

textbox1 maxlength 30, autotab true
textbox2 maxlength 30, autotab true
textbox3 maxlength 30, autotab true
textbox4 maxlength 8

'Info left out

When textbox1 is selected and you paste it will convert it to the following:

textbox1 value = 1002 this village
textbox2 value = this city
textbox3 value = this country
textbox4 value = post code

 

[chrisbelfast]

Just for everyone else, i ended up doing a work around as below:

'user pastes into a textbox which once updated then populates the range
'to be cleaned up
Worksheets("sheet1"Range("A1").PasteSpecial
Application.CutCopyMode = False

Cells(Rows.count, "A").End(xlUp).Select

x = split(ActiveCell.Value, " ", 2)

Range("B1").Select
For i = 0 To UBound(x)
ActiveCell.Value = x(i)
ActiveCell.Offset(1, 0).Select
Next i

a = Range("C1").Value
b = Range("C2").Value

Cells(Rows.count, "A").End(xlUp).Select
ActiveCell.Clear
Cells(Rows.count, "A").End(xlUp).Select
ActiveCell.Value = a
Cells(Rows.count, "A").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = b

'checks if the last 2 lines are identical and deletes the last line
'eg if the address has no post code the last 2 lines will always be the same
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
ActiveCell.Clear
End If

'trims all spaces before and after post code/last line
MyString = ActiveCell
TrimString = LTrim(RTrim(MyString))
ActiveCell = TrimString

'the user then copies the addressee's and once copied then triggers the
'next stage, in my case it was to press a command button that created and
'saved a word doc with the details populated by the userform

range A10 formula = "=TRIM(B10)"
range A11 formula = "=TRIM(B11)"
range A12 formula = "=IF(B11="",B10,A10 &" & " & A11)"

'replaces "&" and " and " with a space in the addressee section
If TextBox16.CanPaste = True Then
Range("B10").PasteSpecial
Application.CutCopyMode = False
ActiveCell.Replace What:=" and ", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="&", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Else
MsgBox "Nothing selected!"
End If

The addressee's will now be on a single line with a & where the may or may not have been before, this allows the "Dear Mr Joe Bloggs & Mrs Jane Bloggs" to be used

Hope this helps anyone who is having similar issues