Excel Forum

Code: Select all

Sub SmartArtTest()

Dim oSALayout As SmartArtLayout
Set oSALayout = Application.SmartArtLayouts(135) 'Get a reference to the "heirarchy" smartart form.

'Create a smartart shape
Set oShp = ActiveWorkbook.ActiveSheet.Shapes.AddSmartArt(oSALayout)

For i = 1 To 5 'clears all the default excel shapes
oShp.SmartArt.AllNodes(1).Delete
Next

For i = 1 To 5
oShp.SmartArt.AllNodes.Add
oShp.SmartArt.AllNodes(i).TextFrame2.TextRange.Text = Range("A" & i).Value
oShp.SmartArt.AllNodes(i).TextFrame2.TextRange.Text = Range("B" & i).Value
Next
End Sub

Code: Select all

Sub SmartArtTest()
    Dim rng As Range, r As Range
    Dim oSALayout As SmartArtLayout
    Set oSALayout = Application.SmartArtLayouts(135) 'Get a reference to the "heirarchy" smartart form.
    
    'Create a smartart shape
    With ActiveWorkbook.ActiveSheet
        If .Shapes.Count > 0 Then .Shapes(1).Delete
        Set oShp = .Shapes.AddSmartArt(oSALayout)
        Set rall = .Range("a1", .Range("a" & .Rows.Count).End(xlUp))
    End With
    For i = 1 To oShp.SmartArt.AllNodes.Count - 1 'clears all the default excel shapes
        oShp.SmartArt.AllNodes(1).Delete
    Next
    
'    For i = 1 To 5
    i = 1
    For Each r In rall
        oShp.SmartArt.AllNodes.Add
        oShp.SmartArt.AllNodes(i).TextFrame2.TextRange.Text = Range("A" & i).Value
        oShp.SmartArt.AllNodes(i).TextFrame2.TextRange.Text = Range("B" & i).Value
        i = i + 1
    Next r
    oShp.SmartArt.AllNodes(oShp.SmartArt.AllNodes.Count).Delete
End Sub

Code: Select all

'Other code
    For Each r In rall
        oShp.SmartArt.AllNodes.Add
        oShp.SmartArt.AllNodes(i).TextFrame2.TextRange.Text = Range("B" & i).Value
        oShp.SmartArt.AllNodes(i).TextFrame2.TextRange.Text = oShp.SmartArt.AllNodes(i).TextFrame2.TextRange.Text & _
            vbCrLf & Range("A" & i).Value
        i = i + 1
    Next r
'Other code