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?

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
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?
.

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

--
Regards,
Tom Ogilvy

"Patrick Molloy" wrote in message
...
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
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?
.

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" wrote in message
...
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?

If I understand you
right click on the auto shape(oval) add textin 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

"Phil Hageman" wrote in message
...
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?

Patrick, Tom and Don

Thanks for your input - works as desired

Phil