Assign a text to a rectangle

[Alberto Ast]

I have a rectangle I made from the drawing tool bar and then I assigned a
macro to it... can I change the text of the rectangle based on the string
stored in a specific cell?

 

[Jacob Skaria]

Hi Alberto

Since it is an autoshape you need to use code to do that..Try the below.
Place this either in worksheet change event so that the text change will
happen as soon as the text is changed...

Dim sh As Shape
Set sh = ActiveSheet.Shapes("Rectangle 1")
sh.OLEFormat.Object.Text = Range("A1")

If this post helps click Yes

 

[Alberto Ast]

I am not sure where you say to put it but I did put it in the macro where I
change the value of the cell and it works great
Thanks

 

[Jacob Skaria]

Thanks for the feedback. Right click the sheet tab>View code and paste the
below code and try changing the text in cell A1..The shape text will chage
with this...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Dim sh As Shape
Set sh = ActiveSheet.Shapes("Rectangle 1")
sh.OLEFormat.Object.Text = Range("A1")
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[Alberto Ast]

Yes but how will this work if the data and the rectangle are in different
sheets?

 

[Jacob Skaria]

Hi Alberto

Right click the sheet tab where your data is and View code>Paste the code..
Change the sheet name mentioend in the code...I assume the shape is in
Sheet3. and try changing the data..

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Dim sh As Shape
Set sh = Worksheets("Sheet3").Shapes("Rectangle 1")
sh.OLEFormat.Object.Text = Range("A1")
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[Alberto Ast]

Thanks Jacob,

It worked great... you have helped me a lot today... well other times
previously too but specially today... the closing enable command was a very
good one.

It is almost midnoght here so I have to stop now.

AA