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/

Where is the textbox and what kind is is

Userform - ActiveX Textbox
Worksheet - ActiveX Textbox
Worksheet - Drawing Toolbar Textbox

What version of Excel -
if all users will have xl2000 or later

Private Sub TextBox1_AfterUpdate()
Dim rng As Range
varr = Split(TextBox1.Text, " ")
Set rng = Range("A1").Resize(UBound(varr, 1) - LBound(varr, 1) + 1, 1)
rng = Application.Transpose(varr)
For Each cell In rng
cell.Value = Application.Clean(cell.Value)
Next
End Sub

You can use change event of the textbox

Private Sub TextBox1_Change()
Dim varr
ReDim varr(0 To 1)
varr = Split(TextBox1.Text, " ")
If UBound(varr) - 1 250 Then
MsgBox "You have hit the limit"
ReDim Preserve varr(0 To 249)
TextBox1.Text = Join(varr, " ")
End If
End Sub

These both assume activeX textbox and the first assumes on a userform.

--
Regards,
Tom Ogilvy




"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/

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/

Thanks for the help. Answers: Yes, Xl2000 or better. Textbox = active
userform

The code is almost there, but two issues remain:
(1) spaces between words still are not counted as a "word", whil
spaces after periods are....
(2) There is an error generated by [plainttextbox.Text = Join(varr,
")] when the word count exceeds 21.

Here is what I have done with your code. I have a user form with
label named "wordcount".


__________________________________________
Private Sub go_Click()

Dim rng As Range
varr = Split(plaintextbox.Text, " ")
Set rng = Range("A1").Resize(UBound(varr, 1) - LBound(varr, 1) + 1, 1)
rng = Application.Transpose(varr)
For Each cell In rng
cell.Value = Application.Clean(cell.Value)
Next
UserForm1.Hide
_______________________________________
Private Sub plaintextbox_Change()
'
' spaces between words need to be counted as words in the
' word count. e.g. "See Spot Run" = 5 words
Dim varr
ReDim varr(0 To 1)
varr = Split(plaintextbox.Text, " ")
wordcount.Caption = UBound(varr)
If UBound(varr) - 1 = 14 Then
MsgBox "You are reaching the limit of 20 words"
ElseIf UBound(varr) - 1 = 20 Then
MsgBox "You have reached the limit of 20 words"
ReDim Preserve varr(0 To 19)
plainttextbox.Text = Join(varr, " ") '<-------------- creates erro
after 21 words
End I

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

I had this in my textbox

The quick brown fox jumped over the lazy dog's back The quick brown fox
jumped over the lazy dog's back
The quick brown fox jumped over the lazy dog's back The quick brown fox
jumped over the lazy dog's back The quick brown fox jumped over the lazy
dog's back The quick brown fox jumped over the lazy dog's back
The quick brown fox jumped over the lazy dog's back The quick brown fox
jumped over the lazy dog's back The quick brown fox jumped over the lazy
dog's back The quick brown fox jumped over the lazy dog's back
The quick brown fox jumped over the lazy dog's back The quick brown fox
jumped over the lazy dog's back The quick brown fox jumped over the lazy
dog's back The quick brown fox jumped over the lazy dog's back
The quick brown fox jumped over the lazy dog's back The quick brown fox
jumped over the lazy dog's back The quick brown fox jumped over the lazy
dog's back The quick brown fox jumped over the lazy dog's back
The quick brown fox jumped over the lazy dog's back The quick brown fox
jumped over the lazy dog's back

The quick brown fox jumped over the lazy dog's back The quick brown fox
jumped over the lazy dog's back
The quick brown fox jumped over the lazy dog's back The quick brown fox
jumped over the lazy dog's back

The quick brown fox jumped over the lazy dog's

(and more - this was after the warning and it cut it back).

You could certainly make a more sophisticated algorithm to determine the
number of words - considering puntuation and multiple spaces and so forth.
Anyway, I didn't see any 20 word limitation on join.

--
Regards,
Tom Ogilvy


"jasonsweeney " wrote in
message ...
Thanks for the help. Answers: Yes, Xl2000 or better. Textbox = activeX
userform

The code is almost there, but two issues remain:
(1) spaces between words still are not counted as a "word", while
spaces after periods are....
(2) There is an error generated by [plainttextbox.Text = Join(varr, "
")] when the word count exceeds 21.

Here is what I have done with your code. I have a user form with a
label named "wordcount".


__________________________________________
Private Sub go_Click()

Dim rng As Range
varr = Split(plaintextbox.Text, " ")
Set rng = Range("A1").Resize(UBound(varr, 1) - LBound(varr, 1) + 1, 1)
rng = Application.Transpose(varr)
For Each cell In rng
cell.Value = Application.Clean(cell.Value)
Next
UserForm1.Hide
_______________________________________
Private Sub plaintextbox_Change()
'
' spaces between words need to be counted as words in the
' word count. e.g. "See Spot Run" = 5 words
Dim varr
ReDim varr(0 To 1)
varr = Split(plaintextbox.Text, " ")
wordcount.Caption = UBound(varr)
If UBound(varr) - 1 = 14 Then
MsgBox "You are reaching the limit of 20 words"
ElseIf UBound(varr) - 1 = 20 Then
MsgBox "You have reached the limit of 20 words"
ReDim Preserve varr(0 To 19)
plainttextbox.Text = Join(varr, " ") '<-------------- creates error
after 21 words
End IF


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

Tom,

I changed the number from 250 to 20 in the code you sent me to test
when somebody reaches the limit....

With the code as I copied above, I got an error....I just erased the
offending line, and now it works....my code now is:

____________
Private Sub plaintextbox_Change()
Dim varr
ReDim varr(0 To 1)
varr = Split(plaintextbox.Text, " ")
wordcount.Caption = UBound(varr)
If UBound(varr) - 1 = 14 Then
MsgBox "You are reaching the limit of 20 words"
ElseIf UBound(varr) - 1 = 20 Then
MsgBox "You have reached the limit of 20 words"
ReDim Preserve varr(0 To 19)
End If
__________________

Also, on the space as a word issue. If I just hit the space bar, the
spaces count as words. What appears to be happening is that a space
occuring after a word is considered "part" of the preceding word.
Weird.


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

Additionally, I need numbers and punctuation to be counted as separat
words.

e.g.:
[12345] typed into the textbox must equal 5 "words".

[See Spot Sit.] = 6 words (period equals "word")

[I said to the man "you must go."] = 18 words (each quotation mark =
word

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

maybe it is a misunderstanding of intent. When you do the split, the space
between words is removed. That is why I am checking 250 and not 500. I am
not sure what happens with multiple spaces in a row as I am not sure what
you are trying to achieve with your limitation.


I just tested and it appears if a space is present, there will an element in
the array for each side of the space. I tested with 5 spaces in a single
string and got 6 elements (but the elements were empty since there is
nothing between the spaces.

? ubound(split(" "," "))
5
? Len(split(" "," ")(1))
0
0
? ubound(split("1 1 1 1 1 1"," "))
5

so I don't think you are correct in saying the space goes with the word - it
is discarded. With that information, perhaps you can refine the code to
achieve what you want.

--
Regards,
Tom Ogilvy





"jasonsweeney " wrote in
message ...
Tom,

I changed the number from 250 to 20 in the code you sent me to test
when somebody reaches the limit....

With the code as I copied above, I got an error....I just erased the
offending line, and now it works....my code now is:

____________
Private Sub plaintextbox_Change()
Dim varr
ReDim varr(0 To 1)
varr = Split(plaintextbox.Text, " ")
wordcount.Caption = UBound(varr)
If UBound(varr) - 1 = 14 Then
MsgBox "You are reaching the limit of 20 words"
ElseIf UBound(varr) - 1 = 20 Then
MsgBox "You have reached the limit of 20 words"
ReDim Preserve varr(0 To 19)
End If
__________________

Also, on the space as a word issue. If I just hit the space bar, the
spaces count as words. What appears to be happening is that a space
occuring after a word is considered "part" of the preceding word.
Weird.


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