"neowok " wrote in message
...
what i have is a whole bunch of autoshape 'ovals' on one sheet (look
like text bubbles really) and i need a way of reading the value from an
oval. The reason is I am trying to set it up so that i can have a
function or macro or something so that when a user clicks on the
bubble, it takes the text from that bubble, puts it into a box on
sheet2 and presses the 'search' button that I have set up.

I tried recording a macro for clicking the bubble, copying the text in
it and pasting it, but the macro code copied the text literally,
meaning if the text in the box changed, the macro replaces the new text
with the macroed text and pastes that into the sheet2 box. It also
refused to paste the text into a textbox on sheet2, it would only paste
it into a cell, which i can deal with but it would be nice if it pasted
it into a textbox. the important thing though is having it somehow
copy the 'current' value of the oval bubble (especially as i need to
apply this macro to about 150 bubbles and i cant have 150 different
macros).

anyone know how i can do this?



The text is held in the TextFrame object, try something like

Dim mShape as Shape, mFrame as TextFrame
Dim mText as String

Set mShape=ActiveSheet.Shapes(1)
Set mFrame = mShape.TextFrame
mText = mFrame.Characters.Text

Keith