Worf

Organization chart with VBA – Part 3

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,252
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...
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Please can I have a copy of the test workbook.
Many thanks
Peter
 
Hello
I will prepare a suitable test workbook.
 
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
 
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.


1687477268563.png


1687477314258.png


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.

1687477535292.png


Once again - really appreciate your assistance on this.
 
Hi

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

Please try again.

Org_3.xlsm
ABCDEF
1SonFatherDescriptionDescription1OutlinePicture
2GRANDDADTOPdesc1100%pic1
3GRANDMATOPdesc2100%pic2
4DADGRANDDADdesc3100%pic3
5MAGRANDDADdesc4100%Opic4
6CHILD100MAdesc5100%pic5
7CHILD101MAdesc6100%pic1
8CHILD102MAdesc7100%pic2
9CHILD103MAdesc80.8Opic3
10CHILD1DADdesc90.8pic4
11CHILD2DADdesc100.8pic5
12CHILD3DADdesc110.8Opic1
13CHILD4DADdesc120.8pic2
14CHILD5DADdesc130.8pic3
15DOG01CHILD100desc14100%Opic4
16DOG02CHILD100desc15100%pic5
17DOG03CHILD100desc16100%pic1
18DOG04CHILD100desc17100%Opic2
19DOG05CHILD100desc18100%pic3
20BIRD01CHILD103desc190.6pic4
21BIRD02CHILD103desc200.6Opic5
22BIRD03CHILD103desc210.6pic4
23CAT1CHILD5desc220.6pic3
24CAT2CHILD5desc230.6Opic2
25CAT3CHILD5desc long100%pic1
26CAT4CHILD5desc long2100%pic2
27CAT5CHILD5desc long3100%pic3
28CAT6CHILD5desclong490%pic4
fshap
 
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.

1687741007583.png


1687741056384.png
 
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.
 
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.

1687908441378.png


1687908488805.png
 

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top