Dear Experts:

below macro grabs the contents of cell B7 and displays it in a text
box (shape, not a control!). How do I have to re-write the code to
have the contents of the cell linked to the contents of the shape.
That is, whenever the cell contents changes, the contents of the text
box is to be updated as well.

Help is much appreciated. Thank you very much in advance. Regards,
Andreas


Sub AddTextBox()
ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, 97.5,
28.5, 96.75 _
, 17.25).Select
Selection.Characters.Text = Range("B7")
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
End With

Sub AddTextBox()
Dim shp As Shape
Set shp = ActiveSheet.Shapes.AddTextBox( _
msoTextOrientationHorizontal, 97.5, 28.5, 96.75, 17.25)
'shp.DrawingObject.Text = Range("B7")
shp.DrawingObject.Formula = "$B$7" ' or "B7"
With shp.TextFrame.Characters.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
End With

End Sub

Regards,
Peter T

"andreashermle" <(E-Mail Removed)> wrote in message
news:ea1dc0a5-913f-4166-a9b3-(E-Mail Removed)...
> Dear Experts:
>
> below macro grabs the contents of cell B7 and displays it in a text
> box (shape, not a control!). How do I have to re-write the code to
> have the contents of the cell linked to the contents of the shape.
> That is, whenever the cell contents changes, the contents of the text
> box is to be updated as well.
>
> Help is much appreciated. Thank you very much in advance. Regards,
> Andreas
>
>
> Sub AddTextBox()
> ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, 97.5,
> 28.5, 96.75 _
> , 17.25).Select
> Selection.Characters.Text = Range("B7")
> With Selection.Font
> .Name = "Arial"
> .FontStyle = "Regular"
> .Size = 10
> End With

On 15 Dez., 13:33, "Peter T" <peter_t@discussions> wrote:
> Sub AddTextBox()
> Dim shp As Shape
> *Set shp = ActiveSheet.Shapes.AddTextBox( _
> * * * * msoTextOrientationHorizontal, 97.5, 28.5, 96.75, 17.25)
> * * 'shp.DrawingObject.Text = Range("B7")
> * * shp.DrawingObject.Formula = "$B$7" ' or "B7"
> * * With shp.TextFrame.Characters.Font
> * * * * .Name = "Arial"
> * * * * .FontStyle = "Regular"
> * * * * .Size = 10
> * * *End With
>
> End Sub
>
> Regards,
> Peter T
>
> "andreashermle" <andreas.her...@gmx.de> wrote in message
>
> news:ea1dc0a5-913f-4166-a9b3-(E-Mail Removed)...
>
>
>
> > Dear Experts:
>
> > below macro grabs the contents of cell B7 and displays it in a text
> > box (shape, not a control!). How do I have to re-write the code to
> > have the contents of the cell linked to the contents of the shape.
> > That is, whenever the cell contents changes, the contents of the text
> > box is to be updated as well.
>
> > Help is much appreciated. Thank you very much in advance. Regards,
> > Andreas
>
> > Sub AddTextBox()
> > ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, 97.5,
> > 28.5, 96.75 _
> > * * * *, 17.25).Select
> > * *Selection.Characters.Text = Range("B7")
> > * *With Selection.Font
> > * * * *.Name = "Arial"
> > * * * *.FontStyle = "Regular"
> > * * * *.Size = 10
> > * * End With- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -

Hi Peter,

thank you very much for your terrific help. That's it! Regards,
Andreas