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/