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

I tried recording a macro for clicking the bubble, copying the text i
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 tex
with the macroed text and pastes that into the sheet2 box. It als
refused to paste the text into a textbox on sheet2, it would only past
it into a cell, which i can deal with but it would be nice if it paste
it into a textbox. the important thing though is having it someho
copy the 'current' value of the oval bubble (especially as i need t
apply this macro to about 150 bubbles and i cant have 150 differen
macros).

anyone know how i can do this

--
Message posted from http://www.ExcelForum.com

"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

ok thanks ill try that.

one thing im having trouble with is when recording a macro, it refuse
to record the selecting of my textbox, the pasting of the text into th
textbox, or the clicking of the command button.

getting the text into the text box should be easy enough with the cod
above, i can just say textbox2.text = mtext, but i also need it t
press the command button which performs a search based on whats in tha
text box, for some reason the macro wont pick this up so im not sure o
the code for it

--
Message posted from http://www.ExcelForum.com

Sheet1.CommandButton1_Click

In the sheet1 code module you would probably also need to change private to
public

Private Sub CommandButton1_Click()

to

Public Sub CommnandButton1_Click()

--
Regards,
Tom Ogilvy


"neowok " wrote in message
...
ok thanks ill try that.

one thing im having trouble with is when recording a macro, it refuses
to record the selecting of my textbox, the pasting of the text into the
textbox, or the clicking of the command button.

getting the text into the text box should be easy enough with the code
above, i can just say textbox2.text = mtext, but i also need it to
press the command button which performs a search based on whats in that
text box, for some reason the macro wont pick this up so im not sure of
the code for it.


---
Message posted from http://www.ExcelForum.com/

Hi Neowok,

On Sheet1 I've put a bunch of oval Shapes containing text.
I then ran the following sub to assign a macro to each of them.

Sub AssignMacroToAllOvals()
Dim Shp As Shape
For Each Shp In Worksheets("Sheet1").Shapes
If Left(Shp.Name, 4) = "Oval" Then Shp.OnAction = "OvalClick"
Next Shp
End Sub


On Sheet2 there's a Textbox (from the Drawing toolbar)
and a Button (from the Forms toolbar). To this Button, I assigned a
macro called "SearchButtonClick".

In a standard code module, I had the following subroutines.
Sub OvalClick()
Worksheets("Sheet2").TextBoxes(1).Text = _
Worksheets("Sheet1").Shapes(Application.Caller).Te xtFrame.Characters.Text
SearchButtonClick
End Sub

Sub SearchButtonClick()
MsgBox Worksheets("Sheet2").TextBoxes(1).Text
End Sub


I believe this should get you on-track.


Regards,
Vic Eldridge





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?


---
Message posted from http://www.ExcelForum.com/

just trying this and now im getting "sub or function not defined" on th
Sub OvalClick() and cant work out why.

all the code is stuck straight in module1 so this is unde
general/ovalclic

--
Message posted from http://www.ExcelForum.com