Organization chart with VBA – Part 3

[Worf]

Worf submitted a new Excel article:

Organization chart with VBA – Part 3 - This version allows for pictures and two-colored boxes

• This is a continuation of the project mentioned below. To produce the chart, run the main routine.
• The information on each box is now presented in two different colors. These colors come from the corresponding cells at the source table. For more detailed explanations, refer to the previous articles.
• Boxes with pictures are nice, but images take up precious space. I chose to provide an option for pictures by right clicking any sheet cell; the images appear over the...

Read more about this Excel article...

 

[MrPeterHall]

Please can I have a copy of the test workbook.
Many thanks
Peter

 

[Worf]

Hello
I will prepare a suitable test workbook.

 

[Worf]

The workbook

Org_3.xlsm

Shared with Dropbox

www.dropbox.com

 

[MrPeterHall]

Thank you hugely - this is really cool - and has so much potential to help us finally map our staff out in an interactive kind of way....
Peter

 

[AndewY]

Hi Worf,

Super awesome stuff!!!! and big "thank you" for the generosity in spending time creating this workbook.
I've tried adding another line of data in the "tdata" sheet > executed the "main" macro but was getting issues with the chart.
Please see screenshot 1 and 2 (also noticing that the new data line "CAT 6" did not create.

I then closed the file and re-opened the original file and added "CAT 6" to "secdata" tab and executed "main" macro.
This resulted in a different issue but I noticed the new data CAT6 was appearing on fshap table but the charts weren't appearing as they should.
Please see following screenshot.

Once again - really appreciate your assistance on this.

 

[Worf]

Hi

The following table worked for me with Excel 2016, note that it goes at the fshap sheet.

Please try again.

Org_3.xlsm
	A	B	C	D	E	F
1	Son	Father	Description	Description1	Outline	Picture
2	GRANDDAD	TOP	desc1	100%		pic1
3	GRANDMA	TOP	desc2	100%		pic2
4	DAD	GRANDDAD	desc3	100%		pic3
5	MA	GRANDDAD	desc4	100%	O	pic4
6	CHILD100	MA	desc5	100%		pic5
7	CHILD101	MA	desc6	100%		pic1
8	CHILD102	MA	desc7	100%		pic2
9	CHILD103	MA	desc8	0.8	O	pic3
10	CHILD1	DAD	desc9	0.8		pic4
11	CHILD2	DAD	desc10	0.8		pic5
12	CHILD3	DAD	desc11	0.8	O	pic1
13	CHILD4	DAD	desc12	0.8		pic2
14	CHILD5	DAD	desc13	0.8		pic3
15	DOG01	CHILD100	desc14	100%	O	pic4
16	DOG02	CHILD100	desc15	100%		pic5
17	DOG03	CHILD100	desc16	100%		pic1
18	DOG04	CHILD100	desc17	100%	O	pic2
19	DOG05	CHILD100	desc18	100%		pic3
20	BIRD01	CHILD103	desc19	0.6		pic4
21	BIRD02	CHILD103	desc20	0.6	O	pic5
22	BIRD03	CHILD103	desc21	0.6		pic4
23	CAT1	CHILD5	desc22	0.6		pic3
24	CAT2	CHILD5	desc23	0.6	O	pic2
25	CAT3	CHILD5	desc long	100%		pic1
26	CAT4	CHILD5	desc long2	100%		pic2
27	CAT5	CHILD5	desc long3	100%		pic3
28	CAT6	CHILD5	desclong4	90%		pic4
fshap

 

[AndewY]

Hi Worf,

Thanks for replying so quickly - very much appreciated.
I re-tried it and unfortunately it's returning an error.
I also re-downloaded a copy of the workbook - "Org 3.xlsm" from the link in this discussion.
Here is a screenshot of the error in the VBA window and of the workbook where it stopped.
The org chart and shape creation looks to be working but something didn't work to flip the chart vertically.

Once again can't say enough thanks for your generous help.

 

[Worf]

this is probably because you do not have the pictures at the correct path, the code expects something like

c:\test\pic1.png

if you do not want pictures, delete the RecPic call at the end of main routine.

 

[AndewY]

That's super! Worf. That worked!

Unfortunately, as I'm adding more CATs to CHILD5 I've gotten another error.

The steps I performed was -
(1) added CAT6 with same details - update worked
(2) added CAT7 with "desc longer" - update worked
(3) added CAT8 with "desc super long" - error (Could it be a limit on the number of characters?)

Here's are the error screen and tab screen's I'm seeing when the error occurred.