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/