Automate Structure/organizational chart

[Doflamingo]

Hi all,

My goal is to automate a Structure/organizational chart with different elements included in a listbox (userform)

here is my current code on VBA of the command button which display on a excel sheet all the items of the listbox of my userform.

Dim i As Long
For i = 0 To ListBox1.ListCount - 1

Cells(5, i * 2+ 3) = ListBox1.List(i)

Cells(5, i * 2 + 3).Select


With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 144, 136.5, 216, 208.5). _
Select
Selection.ShapeRange.ShapeStyle = msoLineStylePreset15
Selection.ShapeRange.IncrementLeft -24
Selection.ShapeRange.IncrementTop -45
Selection.ShapeRange.ScaleWidth 0, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.25, msoFalse, msoScaleFromTopLeft

Next i


I've succeeded to put a connector to one of the item of the listbox, the next step and goal is to put connector to all of the different item of the listbox

any ideas ?

 

[MickG]

Perhaps something like this:-

[COLOR="Navy"]Sub[/COLOR] MG18Apr01
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Con [COLOR="Navy"]As[/COLOR] Object
ListBox1.List = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Value


[COLOR="Navy"]For[/COLOR] i = 0 To ListBox1.ListCount - 1
        [COLOR="Navy"]With[/COLOR] Cells(5, i * 2 + 3)
            .Value = ListBox1.List(i)
            .HorizontalAlignment = xlCenter
            .Borders.Weight = 3
            [COLOR="Navy"]If[/COLOR] i < ListBox1.ListCount - 1 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Con = ActiveSheet.Shapes.AddConnector(msoConnectorStraight, .Offset(, 1).Left, .Height * 4.5, .Offset(, 2).Left, .Height * 4.5)
                Con.Line.Weight = 2
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] i

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Doflamingo]

Hi,

Thanks for the answer,

However, there is that line of code which doesn't work

ListBox1.List = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Value

VBA editor says:

run time error 381

could not set the list property. Invalid property array index.

That's weird because I applied your macro to a commandbutton of my userform and normally the commandbutton should works with the items included in the listbox

Any ideas ?

 

[Doflamingo]

Ah actually I've erased that line of code

ListBox1.List = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Value

and it links all the item of the listbox but the connector is horizontal and i would like a connector vertical for each item of the listbox. Actually the next step will be to link item of listbox 1 to other item of a listbox 2. But I will begin another thread at that moment.

Currently I try to modify that line of code
Set Con = ActiveSheet.Shapes.AddConnector(msoConnectorStraight, .Offset(, 1).Left, .Height * 4.5, .Offset(, 2).Left, .Height * 4.5)
Con.Line.Weight = 2

to see if I can get for each item of the listbox, vertical connector