none
How to select and name a polyline. RRS feed

  • Question

  • Hi,

    I am creating a spreadsheet whereby a user inputs a set of co-ordinates and on the click of a button, a macro produces a polyline based on the co-ordinates. I previously used the Freeformbuilder method but found that addpolyline better suits my aims. Having used the helpfile in Visual Basic Editor, I'm left with a line of code from which I can't see a way to select the newly created polyline shape. Ideally I want to select the shape, name it, and then change some of the properties.

    I'm quite new to VBA so this is probably a novice question, but I'm stuck and would be very grateful for some help here. The sub is copied below.

    Many thanks
    Jinxtt

    Sub GenerateStructure()
    Dim StrucArray(1 To 6, 1 To 2) As Single
    Set MyRange = Worksheets(2).Range("A6:C8")
    StrucArray(1, 1) = WorksheetFunction.VLookup(Range("B16"), MyRange, 2) + 200
    StrucArray(1, 2) = WorksheetFunction.VLookup(Range("B16"), MyRange, 3) + 300
    StrucArray(2, 1) = WorksheetFunction.VLookup(Range("C16"), MyRange, 2) + 200
    StrucArray(2, 2) = WorksheetFunction.VLookup(Range("C16"), MyRange, 3) + 300
    StrucArray(3, 1) = WorksheetFunction.VLookup(Range("B17"), MyRange, 2) + 200
    StrucArray(3, 2) = WorksheetFunction.VLookup(Range("B17"), MyRange, 3) + 300
    StrucArray(4, 1) = WorksheetFunction.VLookup(Range("C17"), MyRange, 2) + 200
    StrucArray(4, 2) = WorksheetFunction.VLookup(Range("C17"), MyRange, 3) + 300
    StrucArray(5, 1) = WorksheetFunction.VLookup(Range("B18"), MyRange, 2) + 200
    StrucArray(5, 2) = WorksheetFunction.VLookup(Range("B18"), MyRange, 3) + 300
    StrucArray(6, 1) = WorksheetFunction.VLookup(Range("C18"), MyRange, 2) + 200
    StrucArray(6, 2) = WorksheetFunction.VLookup(Range("C18"), MyRange, 3) + 300
    Set DemoA = Worksheets(2)
    DemoA.Shapes.AddPolyline StrucArray
    End Sub
    Friday, June 15, 2012 10:45 AM

All replies

  • You don't have to select the shape to change properties. The AddPolyline method returns a Shape object that you can manipulate:

    Sub GenerateStructure()
        Dim DemoA As Worksheet
        Dim MyRange As Range
        Dim StrucArray(1 To 6, 1 To 2) As Single
        Dim shp As Shape
        Set DemoA = Worksheets(2)
        Set MyRange = DemoA.Range("A6:C8")
        StrucArray(1, 1) = WorksheetFunction.VLookup(Range("B16"), MyRange, 2) + 200
        StrucArray(1, 2) = WorksheetFunction.VLookup(Range("B16"), MyRange, 3) + 300
        StrucArray(2, 1) = WorksheetFunction.VLookup(Range("C16"), MyRange, 2) + 200
        StrucArray(2, 2) = WorksheetFunction.VLookup(Range("C16"), MyRange, 3) + 300
        StrucArray(3, 1) = WorksheetFunction.VLookup(Range("B17"), MyRange, 2) + 200
        StrucArray(3, 2) = WorksheetFunction.VLookup(Range("B17"), MyRange, 3) + 300
        StrucArray(4, 1) = WorksheetFunction.VLookup(Range("C17"), MyRange, 2) + 200
        StrucArray(4, 2) = WorksheetFunction.VLookup(Range("C17"), MyRange, 3) + 300
        StrucArray(5, 1) = WorksheetFunction.VLookup(Range("B18"), MyRange, 2) + 200
        StrucArray(5, 2) = WorksheetFunction.VLookup(Range("B18"), MyRange, 3) + 300
        StrucArray(6, 1) = WorksheetFunction.VLookup(Range("C18"), MyRange, 2) + 200
        StrucArray(6, 2) = WorksheetFunction.VLookup(Range("C18"), MyRange, 3) + 300
        ' Create Shape object
        Set shp = DemoA.Shapes.AddPolyline(StrucArray)
        ' Set some properties of the shape
        shp.Fill.ForeColor.RGB = vbGreen
        shp.Line.DashStyle = msoLineDashDot
    End Sub


    Regards, Hans Vogelaar

    Friday, June 15, 2012 11:18 AM
  • Hi Hans,

    Just tried the above which works beautifully! Thank you for your help; I would never have thought of doing it like that.

    Many thanks

    Jinxtt

    Friday, June 15, 2012 11:45 AM