Search code examples
excelcelltitleshapes

Excel insert cell content as link to inserted shapes

Im trying to have the title of a shape automatically populated with the content of a cell. how can i do this. Almost like using the paste link funntion so the title will change as the cell content changes.

Solution

  • There are two ways:

    1

    This way uses VBA. Add this to that worksheet's code module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Const Shape = 1
    Const LinkedCell = "a1"
    If Replace(Target.Address, "$", "") = UCase$(LinkedCell) Then
        Shapes(Shape).TextFrame2.TextRange = Target
    End If
End Sub

    You can edit the two Constant lines near the top to specify which shape (either by number or name) and you can also specify which cell to 'link' to the shape's text.

    2

    This way does not use VBA. Click the shape to select it. Now, click on the Formula Bar at the top of excel and type an equal sign and then the cell address you wish to link to. For example:

    =A1

    ...and then press ENTER to confirm.