You are correct AltaEgo, I should have read the OP post closer. I thought
it a little odd that someone would want code to select a word from a
pharse. Finding a word in a phrase would make more sense. Or finding a
phrase that contained the word.
Unfortunately, selecting part of a cell's content and then trying to run a
macro with it just is not compatible with Excel.


"AltaEgo" <Somewhere@NotHere wrote in message
...
If you are trying to click on a word in the formula bar and have VBA
select it and use it, I suspect VBA cannot do this. You can select the
single word of you choice, copy (using Ctrl-C), press Enter or Escape and
the word will be available to paste. Once you have it stored in the
clipboard, you can get it into a VB variable from the clipboard if you
set a reference to Microsoft Forms 2.0 Object Library (instructions within
the code below).

Code from : http://www.cpearson.com/excel/Clipboard.aspx

You may do some further reading here to find out how to clear the
clipboard from Excel.


Sub test()

'In VBA module click
'Tools, References
'Check Microsoft Forms 2.0 Object Library

Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
MsgBox S

End Sub

HTH
--
Steve

"Westman" wrote in message
...
Sorry, forgot one thing. How to know what is the current selection in
excel?
because I don't use any input box in my codes.

"Westman" wrote:

Thanks for reply, JLGWhiz and Ryan.

I'm sorry, but I don't quite understand the codes. When you set rng =
Selection, are you refering to Application.Selection?

Dim rng As Range
Dim cell As Range
Dim start_str As Integer
Dim myword As String
Dim MyLen As Integer

myword = InputBox("Enter the search string ")

MyLen = Len(myword)
rng = Globals.ThisAddIn.Application.Selection

For Each cell In rng
start_str = InStr(1, cell.Value, myword, vbTextCompare)
If start_str Then
cell.Characters(start_str, MyLen).Font.ColorIndex =
3
keyword = cell.Characters(start_str, MyLen).Text
MessageBox.Show(keyword)
End If
Next

When I use Application.Selection, it will select all text in the current
active cell. Not the highlighted word. With the above codes, all words
will
change to red colour.

to JLGWhiz:
Just to clarify, the phrase could be anything, and the word that I want
to
look for are not predefined.

Maybe for a simpler explanation, in Excel 2007, using the built-in
thesaurus
function (Review - Thesaurus), I'm able to get the highlighted
word(just a
single word) from any phrase in any cell.