VBA to automatically add text to rectangle

[george hart]

Hi

I have a spreadsheet with numerous tabs that consist of graphs etc...Each tab currently has a rectangle with commentary that is manually entered.

My question then. Is there a way I can code the spreadsheet so that the rectangle automatically updates to capture certain text based on certain cells?

For example, I want a rectangle to read: Range("A1") & Range("B1") & "%" & " which shows a variance of " & Range("A3")

Thanks in advance

 

[VoG]

Try

Sub test1()
ActiveSheet.Shapes("Rectangle 1").TextFrame.Characters.Text = Range("A1").Value & Range("B1").Value & "% which shows a variance of " & Range("A3").Value
End Sub

 

[Andrew Poulsom]

Put a concatenation formula that returns what you want in a spare cell. Then link the text in the rectangle to that cell. To do that select your rectangle, go to the formula bar, type =, point at the cell with your mouse and press Enter.