I would like the user to be able to select a part of the text that is displayed in a textbox (not an ActiveX textbox) and use the selected text as a string in a Sub that runs when the user clicks a button. E.g., there is a textbox with the text "Highlight your favorite color: Red, Blue, Green", the user would use the mouse to select the text "Red" and then click a button that runs a macro and the macro could open a MsgBox that says "You're favorite color is Red".

I asked the question here: http://www.ozgrid.com/forum/showthread.php?t=170275 but got no good answer. I can do the above in an ActiveX textbox, but this textbox is not suitable because I also need to be able to change the color of some words in the textbox.

This code
Sheets("Sheet1").TextBoxes("TextBox 1").text
selects all the text in the box.

I tried the following. I started recording a Macro. Highlighted some of the text with my mouse, did Ctrl-C, selected a cell and did Ctrl-V. The highlighted text was pasted to the cell. I stopped recording the Macro. It sure looks like the behavior I want is possible.

Here is the recorded Macro:

Sub Macro2() 
     '
     ' Macro2 Macro
     '
     
     
     '
    Selection.Copy 
    Range("A5").Select 
    ActiveSheet.Paste 
End Sub
Alright, lets see if that works! I delete the contents of cell A5. I highlight some text in the textbox with my mouse and run the Macro.

Unfortunately, the entire textbox gets copied to cell A5 instead of just the highlighted text.

Any ideas? I'm willing to use something other than a textbox if I can
1) change the font properties (such as color) of some words in the text
2) get the text the user highlights with the mouse