Change SmartArt Layout in VBA

[OllieHosking]

Hi All,

I currently have a VBA macro in excel that is creating and populating a SmartArt Organisation Hierarchy, I want to have another macro afterwards that then changes the layout to a different one.

In my mind this is possible, but I am a shambles when it comes to VBA!

Any help will do.

Thank you,
Oliver

 

[Domenic]

You can assign a layout by index number (ie. the first layout is 1, the second is 2, and so on). Therefore, to assign a layout, let's say the third layout, try . . .

Worksheets("Sheet1").Shapes("Diagram 1").SmartArt.Layout = application.SmartArtLayouts(3)

Change the name of the worksheet and shape accordingly.

Hope this helps!

 

[OllieHosking]

Hello,

I am currently using this line in the macro, but I would like it to create that layout and then change it to another one, is that possible?

Thanks,
Oliver

 

[Domenic]

The following code does the following...

1. creates a smart art diagram on the active sheet
2. assigns the first layout, Layout 1
3. positions the top left corner at cell B2
4. sets the width and height according to the size of B2:G0

    Dim shp As Shape
    Set shp = ActiveSheet.Shapes.AddSmartArt( _
        Layout:=Application.SmartArtLayouts(1), _
        Left:=Range("B2").Left, _
        Top:=Range("B2").Top, _
        Width:=Range("B2:G10").Width, _
        Height:=Range("B2:G10").Height)

Hope this helps!