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/

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/

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/

You will have to parse out the string and apply all your special rules.

--
Regards,
Tom Ogilvy

"jasonsweeney " wrote in
message ...
Additionally, I need numbers and punctuation to be counted as separate
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 = 1
word.


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

Tom,

Thanks for your help. I am still playing around with the code. Ca
you point me in the right direction in parsing out the string fo
particular punctuation? Just a few lines of conde for one punctuatio
mark shoudl get me on the right road.

Again, I need to parse the text so that individual punctuation mark
are counted as "words', or, to be more specific, I need the punctuatio
to parsed out so they occupy their own cell when the text is transpose
into excel cells.

Example 1: [See Spot Sit.] = 6 words, and when transposed needs t
look like (lets assume cells A1:A6):

A1 See
A2
A3 Spot
A4
A5 Sit
A6 .

Example 2: [I said to him "go yonder."]: 14 words

A1 I
A2
A3 said
A4
A5 to
A6
A7 him
A8
A9 "
A10 go
A11
A12 yonder
A13 .
A14

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

Tom,

Thanks for your help. I am still playing around with the code. Can yo
point me in the right direction in parsing out the string fo
particular punctuation? Just a few lines of conde for one punctuatio
mark shoudl get me on the right road.

Again, I need to parse the text so that individual punctuation mark
are counted as "words', or, to be more specific, I need the punctuatio
to parsed out so they occupy their own cell when the text is transpose
into excel cells.

Example 1: [See Spot Sit.] = 6 words, and when transposed needs to loo
like (lets assume cells A1:A6):

A1 See
A2
A3 Spot
A4
A5 Sit
A6 .

Example 2: [I said to him "go yonder."]: 14 words

A1 I
A2
A3 said
A4
A5 to
A6
A7 him
A8
A9 "
A10 go
A11
A12 yonder
A13 .
A14

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

Function ParseStr(sStr1 As String)
Dim varr As Variant
Dim sStr As String
Dim sChr As String
Dim sStr2 As String
Dim bLast As Boolean
Dim i As Long
varr = Empty
Dim ub As Long
sStr = sStr1 & " "
ReDim varr(1 To 1)

If Len(sStr) = 0 Then
varr(1) = ""
ParseStr = varr
Exit Function
End If
sStr2 = Mid(sStr, 1, 1)
ub = 1
For i = 2 To Len(sStr)
sChr = Mid(sStr, i, 1)
If LCase(sChr) = UCase(sChr) Then
bLast = True
ReDim Preserve varr(1 To ub)
varr(ub) = sStr2
ub = ub + 1
sStr2 = sChr
Else
If bLast Then
ReDim Preserve varr(1 To ub)
varr(ub) = sStr2
ub = ub + 1
sStr2 = sChr
bLast = False
Else
sStr2 = sStr2 & sChr
bLast = False
End If
End If
Next
ParseStr = varr
End Function

you can use this to test it. It displays words with - <- on each side
so you can see exaclty what is stored in the array. You wanted every digit,
punctuation mark and space treated as a word, so that is what it does. If
a character is a letter, then Ucase(letter) < lcase(letter) so treat it as
a word, else collect the letters as a string to form a word.

Sub TestParse()
Dim sStr As String, sStr1 As String
Dim v, i As Long
sStr = "See Spot123, 456 Sit."
v = ParseStr(sStr)
For i = LBound(v) To UBound(v)
sStr1 = sStr1 & "-" & v(i) & "< -" & vbNewLine
Next
MsgBox sStr1
End Sub

--
Regards,
Tom Ogilvy


"jasonsweeney " wrote in
message ...
Tom,

Thanks for your help. I am still playing around with the code. Can you
point me in the right direction in parsing out the string for
particular punctuation? Just a few lines of conde for one punctuation
mark shoudl get me on the right road.

Again, I need to parse the text so that individual punctuation marks
are counted as "words', or, to be more specific, I need the punctuation
to parsed out so they occupy their own cell when the text is transposed
into excel cells.

Example 1: [See Spot Sit.] = 6 words, and when transposed needs to look
like (lets assume cells A1:A6):

A1 See
A2
A3 Spot
A4
A5 Sit
A6 .

Example 2: [I said to him "go yonder."]: 14 words

A1 I
A2
A3 said
A4
A5 to
A6
A7 him
A8
A9 "
A10 go
A11
A12 yonder
A13 .
A14 "


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

For anybody following, with Tom's very generous help, the following cod
accomplishes what I expressed above:

* all of this into code for userform1:
** On Userform1 place a large textbox named "Textbox1". Also a comman
button named "commandbutton1". Also a label named "wordcount"

___________________________
Function ParseStr(sStr1 As String)
Dim varr As Variant
Dim sStr As String
Dim sChr As String
Dim sStr2 As String
Dim bLast As Boolean
Dim i As Long
varr = Empty
Dim ub As Long
sStr = sStr1 & " "
ReDim varr(1 To 1)

If Len(sStr) = 0 Then
varr(1) = ""
ParseStr = varr
Exit Function
End If
sStr2 = Mid(sStr, 1, 1)
ub = 1
For i = 2 To Len(sStr)
sChr = Mid(sStr, i, 1)
If LCase(sChr) = UCase(sChr) Then
bLast = True
ReDim Preserve varr(1 To ub)
varr(ub) = sStr2
ub = ub + 1
sStr2 = sChr
Else
If bLast Then
ReDim Preserve varr(1 To ub)
varr(ub) = sStr2
ub = ub + 1
sStr2 = sChr
bLast = False
Else
sStr2 = sStr2 & sChr
bLast = False
End If
End If
Next
ParseStr = varr
End Function


Private Sub CommandButton1_Click()
Dim sStr As String
Dim v, i As Long
sStr = textbox1.Text
v = ParseStr(sStr)
For i = LBound(v) To UBound(v)
Next
Dim rng As Range
varr = v
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
End Sub

Private Sub textbox1_Change()
Dim varr
ReDim varr(0 To 1)
Dim sStr As String
Dim v, i As Long
sStr = textbox1.Text
v = ParseStr(sStr)
varr = v
wordcount.Caption = UBound(varr)
If UBound(varr) = 500 Then
MsgBox "You have exceeded the limit of 500 words"
ReDim Preserve varr(0 To 500)
textbox1.Text = Join(varr, " ")
End If

End Su

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

For giggles,

what would the code be for reconstruction the text inserted in th
column back into a text box in a form?

I enter "See spot run." into the text box in Userform1 above and pres
comman button.

In A1:A6 =

A1 See
A2
A3 spot
A4
A5 run
A6 .
_______________
Now in Userform2, in Textbox2, how do we reconstruct "See spot run." i
Textbox2

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

Textbox2.Text = Textbox1.Text

--
Regards,
Tom Ogilvy

jasonsweeney wrote in message
...
For giggles,

what would the code be for reconstruction the text inserted in the
column back into a text box in a form?

I enter "See spot run." into the text box in Userform1 above and press
comman button.

In A1:A6 =

A1 See
A2
A3 spot
A4
A5 run
A6 .
_______________
Now in Userform2, in Textbox2, how do we reconstruct "See spot run." in
Textbox2?


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

Otherwise you can

set rng = Range(Cells(1,1),Cells(1,1).End(xldown))
sStr = ""
for each cell in rng
sStr = sStr & cell.Value
Next
Textbox2.Text = sStr
--
Regards,
Tom Ogilvy



Tom Ogilvy wrote in message
...
Textbox2.Text = Textbox1.Text

--
Regards,
Tom Ogilvy

jasonsweeney wrote in
message
...
For giggles,

what would the code be for reconstruction the text inserted in the
column back into a text box in a form?

I enter "See spot run." into the text box in Userform1 above and press
comman button.

In A1:A6 =

A1 See
A2
A3 spot
A4
A5 run
A6 .
_______________
Now in Userform2, in Textbox2, how do we reconstruct "See spot run." in
Textbox2?


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

Tom,

The wayt I have my spreadsheet now, I manipulate the text that i
inputed vertically into the cells. I then re-construct their sentence
with the changes made. I do this now by entering into a cel
[=A1&A2&A3&A4....etc.]. I name this cell "text." Then I use a tex
box (drawing object variety) and link it to "text" to display th
sentences in the textbox. The problem is this gets oppresive for a 50
word entry.

But, if there is an easy way to reconstruct the vertical column of tex
back into text in a userform textbox it would be better and quicker...

This code below works, but prints the text in a vertical column in th
text box...I need to tranpose it somehow....
____
Private Sub UserForm_Activate()

Sheet1.Range("A1:A500").Copy
textbox1.Paste

End Su

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

TOm,


Didn't see you last reply. :) Thanks again

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