Ok, here is the challenege.

Background: My spreadhseet allows the user to type sentences into
text box. The individual words in the text box are then parsed an
placed in a worksheet in vertical column "A." In vertical column "B"
use vlookup functions that match the word to a number. In another par
of the worksheet, I have essentially a dictionary, where I have abou
6000 individual words listed in a vertical column. Each word i
assigned a discrete number. (00000, 00001, 00002, etc.). So, afte
typing a sentence in the text box (e.g. "I love green eggs and ham")
the spreadsheet would look like thus:

I 32450
Love 43234
Green 34434
eggs 12304
and 00456
Ham 34543

Problem: Sometimes people type words into the text box that are not i
my "dictonary." When that happens, column B will result in "*NID*"
meaning Not-In-Dictionary. (simple IF function)

What I want to do is to have a macro that, when a word is not in th
dictionary, parses the WORD into letters. So if I type i
"serendipity" into the textbox (which is not in the dictionary), th
macro will translate it to " s e r e n d i p i t y", all letters, whic
will be placed in the vertical column (all letters have respespectiv
numners). Thus, the sentence "I want serendipity" would look:

I 43534
want 45676
s 00345
e 01355
r 13544
e 01355
n 76545
d 33434
i 36567
p 35654
i 36567
t 00345
y 33545

Please see previous posts related to the text box parsing:
http://www.excelforum.com/showthread...hreadid=188803

Any help would be appreciate

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

This code assumes that your Dictionary has a named range of "Dictionary"

Also the Split function is not supported until (I think) Excel 2000, so
you'll need Split97.
Tom has made a 97 version: Do a Google Groups search for: Split97 Tom Ogilvy


Private Sub CommandButton1_Click()
Dim str() As String, strTemp As String
Dim lngIndex As Long, i As Long, j As Long, k As Long

str = Split(TextBox1.Text, " ")

k = 1
On Error Resume Next
For i = LBound(str) To UBound(str)
lngIndex = Application.VLookup(str(i), Range("Dictionary"), 2,
False)
If Err.Number Then
Err.Clear
For j = 1 To Len(str(i))
strTemp = Mid(str(i), j, 1)
lngIndex = Application.VLookup(strTemp, Range("Dictionary"),
2, False)
If Err.Number Then
Err.Clear
MsgBox "letter not in dictionary: " & strTemp
Else
Sheet1.Cells(k, 1).Value = strTemp
Sheet1.Cells(k, 2).Value = lngIndex
k = k + 1
End If
Next
Else
Sheet1.Cells(k, 1).Value = str(i)
Sheet1.Cells(k, 2).Value = lngIndex
k = k + 1
End If
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"jasonsweeney " wrote in
message ...
Ok, here is the challenege.

Background: My spreadhseet allows the user to type sentences into a
text box. The individual words in the text box are then parsed and
placed in a worksheet in vertical column "A." In vertical column "B" I
use vlookup functions that match the word to a number. In another part
of the worksheet, I have essentially a dictionary, where I have about
6000 individual words listed in a vertical column. Each word is
assigned a discrete number. (00000, 00001, 00002, etc.). So, after
typing a sentence in the text box (e.g. "I love green eggs and ham"),
the spreadsheet would look like thus:

I 32450
Love 43234
Green 34434
eggs 12304
and 00456
Ham 34543

Problem: Sometimes people type words into the text box that are not in
my "dictonary." When that happens, column B will result in "*NID*",
meaning Not-In-Dictionary. (simple IF function)

What I want to do is to have a macro that, when a word is not in the
dictionary, parses the WORD into letters. So if I type in
"serendipity" into the textbox (which is not in the dictionary), the
macro will translate it to " s e r e n d i p i t y", all letters, which
will be placed in the vertical column (all letters have respespective
numners). Thus, the sentence "I want serendipity" would look:

I 43534
want 45676
s 00345
e 01355
r 13544
e 01355
n 76545
d 33434
i 36567
p 35654
i 36567
t 00345
y 33545

Please see previous posts related to the text box parsing:
http://www.excelforum.com/showthread...hreadid=188803

Any help would be appreciated


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

Don't need Split97. I use XL2002.

Thnx

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

Rob,

I am trying to modify your code so that it executes in th
textbox1_change event. Let me explain.

I am trying to get it so that if the user enters a word in textbox
that is not in the dictionary, then a message comes up "word not i
dictionary" and then the word is parsed into individual letters an
placed back into the textbox as the person types.

I am not having much success so far. There are several problems t
overcome. The first is that I don't know how to add text into
textbox that already contains sub-strings....if I set textbox.value
str(1), then all the previous text is overwritten by the new value.
have tried using the clipboard as a temporary repository for th
existing text, then paste it back and add-on the sub-strings, but
have not been successful with that either.

Second, the str = Split(TextBox1.Text, " ") command acts different whe
in the textbox1_change event. The code seems to trigger whenever an
two sub-strings are joined together (e.g. try typing "the" and the cod
does not find "th" in the dictionary.) It needs to check for th
string using the " " delimiter. Thus, the code should not check to se
if the word is in the dictionary until the space bar is pressed...t
add a level of complexity here, " " is necessarily in the dictonar
itself as a "word". (e.g. the space character is assigned its own
digit code number.)

To reproduce what I have, I typed in separate cells in a vertica
column: "the", "quick", "brown", "fox", "a", "b", "c", ... "z", "".
(*** Note the space character). I then named this range "dictionary")

I have userform3 code as follows:
_________________________________________________
Private Sub CommandButton1_Click()
Dim str() As String
Dim i As Long, k As Long

str = Split(TextBox1.Text, " ")

k = 1
For i = LBound(str) To UBound(str)
Sheet1.Cells(k, 1).Value = str(i)
k = k + 1
Next

UserForm3.Hide
End Sub

Private Sub TextBox1_Change()
Dim str() As String, strTemp As String
Dim lngIndex As Long, i As Long, j As Long, k As Long

str = Split(TextBox1.Text, " ")

k = 1
On Error Resume Next
For i = LBound(str) To UBound(str)
lngIndex = Application.VLookup(str(i), Range("Dictionary"), 2, False)
If Err.Number Then
Err.Clear
UserForm3.TextBox1.Copy
MsgBox "Word not in dictionary: '" & str(i) & "' ;Parse Required."
For j = 1 To Len(str(i))
strTemp = Mid(str(i), j, 1)
lngIndex = Application.VLookup(strTemp, Range("Dictionary"), 2, False)
If Err.Number Then
Err.Clear
MsgBox "letter not in dictionary: " & strTemp
Else
'
' requirements:
' needs to (1) erase the word not in dictionary from the textbox
'leaving the rest of the text in place,
' (2) parse the word into individual letters with a space between
' each letter and a space at the end, (3) needs to insert this
' new string in the textbox in the place where the word-not-in
' -dictonary existed, and
' (4) allows the user to continue typing his or her paragraph
'
UserForm3.TextBox1.Value = strTemp

k = k + 1
End If
Next
End If
Next

End Su

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

The Change event doesn't know that you've just typed a space.
You probably want to look at the BeforeUpdate event and process everything
at the end
or the KeyUp event, where you check after each word.

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = Asc(" ") Then
Beep
End If
End Sub

An approach might be to reprocess the whole sentence every word. Just to
accommodate copy-paste actions / inserting words between words etc..




--
Rob van Gelder - http://www.vangelder.co.nz/excel


"jasonsweeney " wrote in
message ...
Rob,

I am trying to modify your code so that it executes in the
textbox1_change event. Let me explain.

I am trying to get it so that if the user enters a word in textbox1
that is not in the dictionary, then a message comes up "word not in
dictionary" and then the word is parsed into individual letters and
placed back into the textbox as the person types.

I am not having much success so far. There are several problems to
overcome. The first is that I don't know how to add text into a
textbox that already contains sub-strings....if I set textbox.value =
str(1), then all the previous text is overwritten by the new value. I
have tried using the clipboard as a temporary repository for the
existing text, then paste it back and add-on the sub-strings, but I
have not been successful with that either.

Second, the str = Split(TextBox1.Text, " ") command acts different when
in the textbox1_change event. The code seems to trigger whenever any
two sub-strings are joined together (e.g. try typing "the" and the code
does not find "th" in the dictionary.) It needs to check for the
string using the " " delimiter. Thus, the code should not check to see
if the word is in the dictionary until the space bar is pressed...to
add a level of complexity here, " " is necessarily in the dictonary
itself as a "word". (e.g. the space character is assigned its own 5
digit code number.)

To reproduce what I have, I typed in separate cells in a vertical
column: "the", "quick", "brown", "fox", "a", "b", "c", ... "z", "".
(*** Note the space character). I then named this range "dictionary")

I have userform3 code as follows:
_________________________________________________
Private Sub CommandButton1_Click()
Dim str() As String
Dim i As Long, k As Long

str = Split(TextBox1.Text, " ")

k = 1
For i = LBound(str) To UBound(str)
Sheet1.Cells(k, 1).Value = str(i)
k = k + 1
Next

UserForm3.Hide
End Sub

Private Sub TextBox1_Change()
Dim str() As String, strTemp As String
Dim lngIndex As Long, i As Long, j As Long, k As Long

str = Split(TextBox1.Text, " ")

k = 1
On Error Resume Next
For i = LBound(str) To UBound(str)
lngIndex = Application.VLookup(str(i), Range("Dictionary"), 2, False)
If Err.Number Then
Err.Clear
UserForm3.TextBox1.Copy
MsgBox "Word not in dictionary: '" & str(i) & "' ;Parse Required."
For j = 1 To Len(str(i))
strTemp = Mid(str(i), j, 1)
lngIndex = Application.VLookup(strTemp, Range("Dictionary"), 2, False)
If Err.Number Then
Err.Clear
MsgBox "letter not in dictionary: " & strTemp
Else
'
' requirements:
' needs to (1) erase the word not in dictionary from the textbox
'leaving the rest of the text in place,
' (2) parse the word into individual letters with a space between
' each letter and a space at the end, (3) needs to insert this
' new string in the textbox in the place where the word-not-in
' -dictonary existed, and
' (4) allows the user to continue typing his or her paragraph
'
UserForm3.TextBox1.Value = strTemp

k = k + 1
End If
Next
End If
Next

End Sub


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

Rob,

Thanks again for the sounding board.

I like the key_up event idea. I am going to work with that....

Do you know any other way besides cutting the sentences, and the
repasting, to insert text into an existing string as the person types?
I was trying to play with Curline, curX, etc., and subtract/ad
substrings based on the curX count....but no luck there.

The trick with the copy/paste technique would be apending the ne
string (e.g. "S E R I N D I P I T Y") to the existing tex
string....once you paste the text into the textbox from the clipboard
you are back to the problem of adding on new text onto the old....that
my dillema

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

There are some properties of the TextBox like SelStart and SelLen (from
memory) which tell you the position of the cursor. Could you work with that?

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"jasonsweeney " wrote in
message ...
Rob,

Thanks again for the sounding board.

I like the key_up event idea. I am going to work with that....

Do you know any other way besides cutting the sentences, and then
repasting, to insert text into an existing string as the person types?
I was trying to play with Curline, curX, etc., and subtract/add
substrings based on the curX count....but no luck there.

The trick with the copy/paste technique would be apending the new
string (e.g. "S E R I N D I P I T Y") to the existing text
string....once you paste the text into the textbox from the clipboard,
you are back to the problem of adding on new text onto the old....thats
my dillema.


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

Ok...

here is what I am working with now...My theory is that I can use th
key_up event to test whether the user presses the space bar. If the
do, then the code executes and checks the whole textbox to see if eac
word is in the dictionary, and parse if not, etc.

But I can't seem to make it work....Here is what I have:

________________________
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger
ByVal Shift As Integer)


If UserForm3.TextBox1.SelStart < Chr(32) Then Exit Sub

'[THE REST OF THE ROUTINE]

End Sub
________________________

Another way to ask this question is: how do you launch a code wheneve
the user depresses the space bar while typing in a text box

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

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = Asc(" ") Then
SomeCode
End If
End Sub

Sub SomeCode()
Beep
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"jasonsweeney " wrote in
message ...
Ok...

here is what I am working with now...My theory is that I can use the
key_up event to test whether the user presses the space bar. If they
do, then the code executes and checks the whole textbox to see if each
word is in the dictionary, and parse if not, etc.

But I can't seem to make it work....Here is what I have:

________________________
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)


If UserForm3.TextBox1.SelStart < Chr(32) Then Exit Sub

'[THE REST OF THE ROUTINE]

End Sub
________________________

Another way to ask this question is: how do you launch a code whenever
the user depresses the space bar while typing in a text box?


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

Ok,

For those following this thread, I now have code that performs the wa
I want, but I still would rather do the same solution programicall
instead of bouncing between worksheet functions and VBA. Read on.


My question for Rob, or anyone else watching, is whether they see a wa
to do what I have done programatically instead of using the workshee
for part of functionality. As you will note from the explanatio
below, I have achieved what I want by placing some text onto th
workbook and processing it there with functions and then bringing th
text back into the text box....but this slows down the cod
somewhat...(in my end solution the user needs to be able to enter a 50
word paragraph into the text box.)

To reproduce exactly what I have:

(1) Copy this and insert it into range D1:D34:

=""
quick
brown
fox
jumped
over
lazy
dog
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
q
r
s
t
u
v
w
x
y
z

(2) Copy the following into range E1:E34
9898
2575
7565
3435
3676
1564
7564
3765
1111
2222
3333
4444
5555
6666
7777
8888
9999
1122
2233
3344
4455
5566
6677
7788
8899
9911
2323
2424
2525
2626
2727
2828
2929
3030

IMPORTANT: Highlight the range D1:E34 and name the range "dictionary"

(3) In cell C1 insert this formula:

=IF(AND(A1="",B1=""),"*EMPTY*",IF(A1="",B1,A1))

copy it down to cell C50. Name this range "testrange".

(4) Create Userform1. Add one text box "Textbox1", and one comman
button "CommandButton1". In properties of textbox1, set multline t
TRUE. Insert the following code into the Declaration section:

_______________
Private Sub CommandButton1_Click()
Dim str() As String
Dim i As Long, k As Long

str = Split(TextBox1.Text, " ")

k = 1
For i = LBound(str) To UBound(str)
Sheet1.Cells(k, 1).Value = str(i)
k = k + 1
Next

UserForm1.Hide
End Sub
_______________
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger
ByVal Shift As Integer)

Dim str() As String, strTemp As String
Dim lngIndex As Long, Bindex As Long, i As Long, j As Long, k As Long
cellStr As Long
Dim n As Long
Dim test As Long
'
If KeyCode = Asc(" ") Then
str = Split(TextBox1.Text, " ")
k = 1
m = 1
' Check first letter to last letter, then look to see if string i
dictionary
On Error Resume Next
For i = LBound(str) To UBound(str)
lngIndex = Application.VLookup(str(i), Range("dictionary"), 2
False)
'
' If NOT in Dictionary then START PARSE CODE
If Err.Number Then
Err.Clear
MsgBox "Word not in dictionary: '" & str(i) & "' ;Pars
Required."
For j = 1 To Len(str(i))
strTemp = Mid(str(i), j, 1)
lngIndex = Application.VLookup(strTemp
Range("dictionary"), 2, False)
If Err.Number Then
Err.Clear
MsgBox "sub-string not in dictionary: " & strTemp
Else
Sheet1.Cells(k, 2).Value = strTemp
k = k + 1
End If
Next
Else
Sheet1.Cells(k, 1).Value = str(i)
k = k + 1
End If
Next

Set rng = Range("testrange")
pStr = ""
For Each cell In rng
' Look to see of the string "*EMPTY*" is in the cell
If cell.Value < "*EMPTY*" Then
pStr = pStr & cell.Value & Chr(32)
End If
Next
TextBox1.Text = pStr

End If
End Sub
_______________

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