Get the user selected text from a textbox in VBA

[LoomisP]

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

[Leith Ross]

Hello LoomisP,

The problem with a non ActiveX TextBox is VBA can not act on it because Excel is in Edit Mode. Anytime you Edit a cell or object in Excel, VBA is automatically locked out until the Editing is finished and once that has happen, the text is no longer selected.

Using multicolor characters is not a standard feature with ActiveX objects. Typically, all characters must be same color. To get the multicolor fonts like in Excel and in Word, you need to use a Rich Text Format object, which is an ActiveX component. Unfortunately, because this type of control poses a security risk, VBA users do not have programming access to RTF objects.

[LoomisP]

Leith, thanks for your help.

I don't see how to add a Rich Text Format object to my sheet. It's not listed under Developer -> Insert (I checked the list of More Controls as well). I am using Excel 2007.

Before I go too far down this road, you think it will be possible using an RTF textbox to change colors of individual words and use the user selected text in a SUB? (right now I don't care how hard it is, just whether or not it's possible)

[Leith Ross]

Hello LoomisP,

Looking back on these posts, I am not sure I understand what you want to do exactly. Did you want the user to select a portion of text in TextBox or other control and run a macro that would ask which color the selected font should be? If so, you could do this in Word more easily than Excel.

[LoomisP]

I'll try to be more clear about the end goal. Unfortunately, this is only a part of the software and I don't have the option of switching to Word.

The user will be shown a section of text. Some keywords in the text will be color coded. (This much is easy enough to do with a standard Text Box from the Insert menu.) The user then has the option to report keywords that should have been color coded but weren't. It would be nice if the user could just highlight the word using the mouse and click a button. An ActiveX TextBox allows this because I can get the user selected text and add it to the keyword dictionary.

[Andy Pope]

Does it have to be on the worksheet? Can you use a userform to present and gather the information.

I thinking of using multiple label controls, one per word, that you can format individually.

[LoomisP]

Andy, sounds like I'd be getting in to the typesetting business trying to arrange all the labels for each passage of text. I think it would be a ton of work to try to implement your suggestion.

[Andy Pope]

I haven't seen any real data so can not say. But given the textbox approach is a non starter due to lack of selected text properties it might be a valid solution.