From VBA-GUI textbox to cells

  • Thread starter Thread starter Scandinavian
  • Start date Start date
S

Scandinavian

I need to move textlines from a textbox (NB!pasted by the user)on a
VBA-GUI into several cells on a excell sheet.
The problem is that all the text from the textbox ends up in one cell,
and not as it is supposed to do: one line in textbox should end up in
one cell on the sheet.

I guess, I have to locate line shifts in the text that the user pasted
in the textbox. - but how?

I hope anyone has a solution


:confused:
 
Hard coded line shift are done with Chr(13) & Chr(10) (vbCrLf constant).

Private Sub CommandButton1_Click()
Dim sLine As String
Dim i As Long, j As Long
Dim rw As Long, sChr As String
rw = 10
sLine = UserForm1.TextBox1.Text
j = 0
i = 1
Do While i <= Len(sLine)
sChr = Mid(sLine, i, 1)
If sChr <> vbCr Then
sStr = sStr & sChr
Else
Cells(rw + j, 1) = sStr
sStr = ""
j = j + 1
' skip vbLf character
i = i + 1
End If
i = i + 1
Loop
Cells(rw + j, 1) = sStr

End Sub
 
Back
Top