Jason,

Here's a basic solution for Q1.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long
Dim iPos As Long
Dim iPrev As Long

iPrev = 0
i = 1
Do
iPos = InStr(iPrev + 1, TextBox1.Text, " ")
If iPos 0 Then
ActiveSheet.Cells(i, "A").Value = Mid(TextBox1.Text, iPrev + 1,
iPos - iPrev)
i = i + 1
ActiveSheet.Cells(i, "A").Value = " "
i = i + 1
iPrev = iPos
End If
Loop Until iPos = 0
ActiveSheet.Cells(i, "A").Value = Right(TextBox1.Text,
Len(TextBox1.Text) - iPrev)

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jasonsweeney " wrote in
message ...
What I want to do is this:

In a user form, I want the user to be able to type a long message in a
text box. After they are done, I want to pull out each word and place
each word in verticle column.

For example, if I typed "The quick brown fox jumped over the lazy dog"
in the text box, Excel would place once word each in cells A1:A9, so A1
would contain the text "The", A2 = [space], A3 = "quick", etc.

Additionally, I want to limit the user's entries to 500 words, with a
"space" between words counting as one word. I want to display the word
count to the user AS they are typing out the message in the user form.

Any ideas?


---
Message posted from http://www.ExcelForum.com/