Organization chart with VBA – Part 2

[Worf]

Hi Rajat

Do you still need the file ?

 

[kARTIK12345]

Excel file is required.

 

[countryfan_nt]

Great idea and an amazing code, could you please share the workbook?
Seriously thank you very much!

 

[Worf]

I will prepare a test workbook as soon as possible.

 

[Worf]

The workbook

Org 2.xlsm

Shared with Dropbox

www.dropbox.com

 

[CantGetRight]

I know this post is quite old at this point. Amazing code so thank you!

Any idea if there is a way to make this work without needing to format the excel table in such a specific way. My starting point is a 500+ organization and my plan was to filter down to specific teams but having the supervisor setup in excel like this seems like a brain twister without doing manually.

Thanks,
Mark

 

[Worf]

Hello Mark

The code requires a source table formatted that way. I suppose your original table can be automatically filtered and converted to my format using VBA.

I suggest you post a forum question providing the necessary details.

 

[CantGetRight]

Hello Mark

The code requires a source table formatted that way. I suppose your original table can be automatically filtered and converted to my format using VBA.

I suggest you post a forum question providing the necessary details.

Understood and thank you for replying. Do you know if the data is structured with more rows than the colored table in your example will it break?

I notice sometimes with too many rows the output becomes hard to visualize and the boxes are too large and overlap, making it very hard to see the reporting lines. Any idea how to solve?

 

[Worf]

Theoretically there is not a row limit.

I have a total of five articles about this kind of chart, see if there is another that yields better results, such as the one that allows to customise the box positioning.

 

[LearningExcel456]

@Worf Thanks for this. This is the closest I've come to finding a practical solution for converting ownership data to a visual chart. I have two questions based off this article.

1. How easy would it to add more descriptive text columns similar to current "Description" column? I need four in total.
2. I noticed the ultimate parent relationship has to be in row 2 of source table? Does it have to be?
3. I receive an R offset Object Variable error when one of the "son" elements (A) is owned by two "father" elements (B and C) and one of those "father" elements (B) is also owned by (C). Below for example. Any way to solve this?

sonfatherdescription1
bbdd100%
aabb90%
aacc10%
ccbb100%

4. Smaller issue is after running the macro it keeps pasting a table of all the son rows into range AA21:AE37 in sheet fshap. The org chart produced looks fine though. This is using the same source table from this article. Would you know why?