VBA to create organisation chart

[jbesclapez]

Hi there,

I found a great macro on the web that nearly does my job. But only nearly. Unfortunately this code is really high level, and I have no idea how to solve it. Super challenging!
You can download the file here:
orgaproblem
And what I would like to achieve is below: (see picture)

Note that there is a limitation.
The children of G in the second table should only show once in the hierarchy otherwise it will be to messy.

Thanks for your help

 

[Worf]

We could use this one:

Organization chart with VBA

 

[yky]

We could use this one:

Organization chart with VBA

Possible to reverse the vertical order of the chart, i.e. having AA on the top instead of bottom?

 

[Worf]

Sure, see post #44 of the original thread below. Maybe I should offer this option…

Add New Variable to Shapes

Hi all, Here is the code to create a structure chart from range A to range C of the sheet ‘’BD’’ Here is a screenshot of the sheet ‘’BD’’ https://www.dropbox.com/s/vyik32v24dp2ysm/sheet%20BD.png?dl=0 There is the ‘’Big father‘’ in A2 which is the ‘’Boss’’ The column B states all the ‘’sub...

www.mrexcel.com

 

[yky]

Thanks. Got it.

What is "Dim lr%"?

 

[Worf]

It is an integer variable:

Sub intvar()
Dim var1 As Integer, var2%

var1 = 5
var2 = 9

MsgBox Len(var1) & vbLf & Len(var2)

End Sub

 

[yky]

Got the error "the index into the specified collection is out of bound" at this line:

For i = 1 To 5
   [COLOR=rgb(184, 49, 47)] oshp.SmartArt.AllNodes(1).Delete        ' initial nodes[/COLOR]

 

[Worf]

I am going offline now for dinner.

We are hijacking the thread; can you start a new one and inform the link?

 

[jbesclapez]

I am going offline now for dinner.

We are hijacking the thread; can you start a new one and inform the link?

Hi Guys, and thanks for your replies. No worries about the hijack, it is not, you are talking about the solution ehheheh!!!
@Worf what you did is really cool! Unfortunately like @yky I get an error and then it closes my excel.

For me there error seems to be there :

For i = 1 To 5
    oshp.SmartArt.AllNodes(1).Delete        ' initial nodes
Next

Do you have any idea of this error?

Thanks

 

[Worf]

Do you have any idea of this error?

Yes. I need to know your Excel version.