Automating the "Oval" Object

P

Phil Hageman

Is it possible to link text in the Oval object (from the Drawing toolbar) with text in a merged cell range - the object being when a user types text in one, it appears in the other? If so how would this be done?
 
P

Patrick Molloy

This can be done using the sheet's Change event. This is
fired when a new value is entered into a cell.

The example places the text from cell C3 into the oval.
I've drawn an oval and left the default name 'Oval 1' in
the code.

Paste the following code into your worksheet's code page.
To open the code page, right click on the sheet's tab and
select the last menu item: 'View Code'


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$3" Then

ActiveSheet.Shapes("Oval 1").Select
Selection.Characters.Text = Target.Text


Target.Select
End If

End Sub


First the code check's that the changed cell is C3. If it
is the oval is selected, the text is changed and then the
cell is re-selected.

It's a pity, but this is one object that you have to
select for this.

Patrck Molloy
Microsoft Excel MVP

-----Original Message-----
Is it possible to link text in the Oval object (from the
Click to expand...
Drawing toolbar) with text in a merged cell range - the
object being when a user types text in one, it appears in
the other? If so how would this be done?
 
T

Tom Ogilvy

Select the oval

go to the formula bar

enter
=A1
and hit return.

change A1 to the upper left cell in the merged cell range.

--
Regards,
Tom Ogilvy

Phil Hageman said:
Is it possible to link text in the Oval object (from the Drawing toolbar)
Click to expand...
with text in a merged cell range - the object being when a user types text
in one, it appears in the other? If so how would this be done?
 
D

Don Guillett

If I understand you
right click on the auto shape(oval) >add text>in the FORMULA bar type in =n3
where n3 is the first of your merged cells. Then when n3 is changed the text
will appear in the shape.

--
Don Guillett
SalesAid Software
[email protected]
Phil Hageman said:
Is it possible to link text in the Oval object (from the Drawing toolbar)
Click to expand...
with text in a merged cell range - the object being when a user types text
in one, it appears in the other? If so how would this be done?
 
T

Tom Ogilvy

Actually you don't have to select it. You can use

Private Sub Worksheet_Change(ByVal Target As Range)
Dim shp As Shape
If Target.Address = "$C$3" Then
Set shp = ActiveSheet.Shapes("Oval 2")
shp.TextFrame.Characters.Text = Target.Text
End If

End Sub

or

Activesheet.Ovals("Oval 2").Text = Target.Text
 
Top