Automatically create and name an Object from a set list

Question

I'm currently trying to auto name the objects from a referenced list rather than just have the object text stated in the script.

How do I get the script to reference a separate worksheet called Process Steps where the text value is in cell C7 instead of entering the statement in the script as Step 1

ActiveSheet.Shapes.AddShape(msoShapeRectangle, 50, 50, 100, 50).Select
Selection.Formula = ""
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset40
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Step1"

Try `Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Sheets("YourWorksheetName").Range("C7").Value` — Peter Albert, Feb 06 '14 at 08:20

score 1 · Accepted Answer · edited May 23 '17 at 12:28

Peter has already mentioned how to pick up a value from another cell. Taking this a bit ahead.

Please avoid the use of .Select/.Activate INTERESTING READ

Is this what you are trying?

Sub Sample()
    Dim shp As Shape

    Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 50, 50, 100, 50)

    With shp.OLEFormat.Object
        .Formula = ""

        .ShapeRange.ShapeStyle = msoShapeStylePreset40

        .ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        ThisWorkbook.Sheets("Process Steps").Range("C7").Value
    End With
End Sub

Automatically create and name an Object from a set list

1 Answers1