Select a word in an cell

[Westman]

Hi,

I'm facing a problem in developing my excell addin. How do I select just one
word from a sentences in a single cell? For example:-

I like to eat fried chicken. --> This sentence is in a cell.

Right now, I just want to select word "eat" from the above sentence. Is this
posible? If posible, how do I accomplish that?

Regards,
Westman

 

[Greg Glynn]

Westman,

I don't think you can. Excel doesn't allow you to access the Selected
Characters in a Cell programatically.

But don't take my word for it.

 

[NickHK]

AFAIK, the only way in VBA is with SendKeys:

Private Sub CommandButton1_Click()
Dim StartPos As Long

Const WordToHiLite As String = "eat"
Const RangeToSearch As String = "B6"

With Range(RangeToSearch)
StartPos = InStr(1, .Value, WordToHiLite)

If StartPos = 0 Then Exit Sub

.Select
SendKeys "{F2}"
SendKeys "{LEFT " & Len(.Value) - StartPos + 1 & "}"
SendKeys "+{RIGHT " & Len(WordToHiLite) & "}"
End With

End Sub

NickHK

 

[OssieMac]

Will this example help?
Sub Test_InString()

Dim myString
Dim myPos

myString = "I like to eat fried chicken"
StrToFind = "eat"

myPos = InStr(1, myString, StrToFind, 1)
If mypos > 0 Then
MsgBox ("Found string starting at character " & mypos)
End If


End Sub

 

[Greg Glynn]

Nick,

I can't get to work for me. The Macro steps execute OK, but the text
doesn't get highlighted.

(It's probably not a bad thing though, I gotta lay off the Fried
Chicken anyway).

Greg

 

[Greg Glynn]

Oh, I see what you've done (execute it via a command button). Yeah
that works.

Greg

 

[Westman]

Hi NIck,

Thanks for your reply. The codes work like a charm. But I think it is useful
only when you know what word to find or grab, is it? Right now I'm developing
a thesaurus for excel and (supposedly) it can find synonym(s) for any word
that I have selected. In your codes, you have define which word should be
search for. And I dont have any clue how to modified those code to make it
get any word at any cell in Excel.

Westman

 

[NickHK]

Make it in a sub and pass the requirements as arguments.
e.g.
Private Sub CommandButton1_Click()
Call HiLiteWord(ActiveCell, Range("A1").Value)
End Sub

Private Sub HiLiteWord(argRange As Range, argWord As String)
Dim StartPos As Long

With argRange
StartPos = InStr(1, .Value, argWord)

If StartPos = 0 Then
MsgBox Chr(34) & argWord & Chr(34) & " was not found in cell " &
argRange.Address
Exit Sub
End If

.Select
SendKeys "{F2}"
SendKeys "{LEFT " & Len(.Value) - StartPos + 1 & "}"
SendKeys "+{RIGHT " & Len(argWord) & "}"
End With

End Sub

NickHK

 

[Westman]

Hi Nick,

Thanks again for the codes. FYI, i'm using VSTO2005 SE. So, I have to modify
the codes first before it can be used. By the way, from your code, isn't the
argument ActiveCell and Range("A1").Value) will have the same value? If yes,
when do the codes return just the selected word?

Regards,
Westman

 

[Westman]

Hi OssieMac,

Thanks for the reply. And yes, your example help me to some extent in the
process of getting to my goal. The codes just find a "whatever" we have
defined only. Still I appreciate your help.

Regards,
Westman

 

[NickHK]

Only if A1 is selected.
And it does not return the selected word; it selects the word in A1 if it is
present in the ActiveCell.

NickHK

 

[Tom Ogilvy]

Since he says he is building a Thesaurus, I believe he wants the user to
select a sentence in a cell, then somehow fire his thesaurus addin which
will find equivalent words to the selected word. So he wants to discover
what word in a cell is selected. Kind the opposite of what you are
offering.

 

[Westman]

Hi Tom,

Yes, that is exactly what I'm looking for. Just selecting one word only and
then called the thesaurus. I'm still working on the solution for it and I
have tried to modify the code given by Nick but still could not get the true
result. Do have any idea on how to to do this?

Regards,
Westman

 

[NickHK]

Tom,
I was going from the initial of "I just want to select word "eat" from the
above sentence".
But now it seems it is other way as yopu describe.

To the OP:
In that case VBA will not help you, as it cannot run in Edit mode, when the
cursor is in a cell.

NickHK

 

[Jim Cone]

For what it is worth...
I've already written a "Thesaurus for Excel" program, and let me tell you,
there isn't any market I can find for that type of utility.
Also, it is not an easy type of program to code and I doubt worth
the time required to do it.
Note: it uses the last word in the cell as the initial word to display equivalents for.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"NickHK" <[email protected]>
wrote in message
Tom,
I was going from the initial of "I just want to select word "eat" from the
above sentence".
But now it seems it is other way as yopu describe.

To the OP:
In that case VBA will not help you, as it cannot run in Edit mode, when the
cursor is in a cell.

NickHK

 

[Westman]

Hi Jim,

"it uses the last word in the cell as the initial word to display
equivalents for."

What do you mean by those phrase? Does it mean that excel start reading a
cell backward from the last to first word?

FYI, I'm bundling the thesaurus with other programs(which contains a
localization spell checker,thesaurus and .... etc-not for english languages.
This is for Office 2007). So in my situation, actually this program is very
useful for my customer especially the gov sector.

Regards,
Westman