CODEWITHSUNDEEP
excelshapesvba
Adrian Gornall
Adrian Gornall

Reputation: 327

Automatically create and name an Object from a set list

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"

Upvotes: 1

Views: 132

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149297

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

Upvotes: 1

Related Questions