Excel Smart Art Family Tree

Excel Smart Art Family Tree

How far back can you trace your family tree? Prof. Lee Townsend, from University of Hartford, has found an interesting new way to draw her family tree — in Excel, using Smart Art and some VBA. Here’s how she created her Excel Smart Art Family Tree.

Here is a screen shot of the Excel Smart Art Family Tree.


The details are below, and I’m also happy to announce the winners in the Peltier Tech Chart Utility Giveaway.

Smart Art Family Tree

You might remember the code that Prof. Townsend shared a couple of years ago, for documenting your VBA procedures.


In the second phase of that project, she developed a mind map, and now she has created the third phase – a Smart Art family tree. Her sample workbook has a list with family member names, and buttons to create a family tree, and clear the tree.


Mac Vs PC Layouts

The code will run on either a Mac or PC, but they use different numbers for the layouts. For example, the Family Tree uses a horizontal Organization Chart layout, which is layout #88 on a Macintosh, but it’s layout #104 on a PC.

  • You can see the layout number differences in this pdf file: Smart Art Layout Numbers Mac and PC
  • There are paste special as picture differences too, described in this pdf file: Paste Special Picture Mac and PC

Fortunately, in the code, Prof. Townsend checks to see which type of computer you’re using, and selects a layout number and picture setting that will work on your machine.

sep = Application.PathSeparator
Select Case sep
    Case "\"    'it's a PC
    Case ":"    'it's a Mac
End Select

Mind Map Family Tree

There is also a Mind Map version of the family tree creator. In addition to the Smart Art family tree, it creates the mind map code for the family tree. You can find this in Part 4 of the project description.


Download the Sample Files

You can download the sample files from Prof. Townsend’s web page, on the University of Hartford site.

  • Scroll down to the section, “Creating a mind map and a SmartArt organization chart of procedure calls in VBA”.
  • There are download links in Part 3 and Part 4 of that section.

Peltier Tech Chart Utility Giveaway Winners

Jon Peltier is releasing his Advanced Excel Charting Utility today, and he donated a copy for a giveaway, that I announced on Tuesday. The deadline was yesterday, at 5 PM, and I have selected the winners.

It was tough to choose the 3 comments that I liked best – thanks for leaving such amazing and thoughtful reasons why you need the utility! I copied the comments and ID numbers into Excel, and read them without the names, to help me make unbiased choices.

Then, from my 3 top picks, I used the RAND function to sort the ID numbers, and the results are listed below. Prize #4 was a random draw from all the other entries.

  1. Grand Prize Winner – 1 copy of Peltier Tech’s new Advanced Excel Chart Utility — JoAnn Paules, with comment 4
  2. Runner-Up Prize Winner – 1 copy of any Contextures product – Trouttrap2, with comment 13
  3. Runner-Up Prize Winner – 1 copy of any Contextures product – CR, with comment 12
  4. Random Draw Prize Winner – 1 copy of any Contextures product – Eric, with comment 17

Congratulations to all the winners! You will be contacted at the email address you provided, and will have 24 hours to claim your prize.

And happy birthday to Jon – thanks for donating a copy of your new charting utility!

Get Your Free Upgrade

Remember, if you previously bought a copy of the standard Excel Charting Utility from PeltierTech, you can get a free upgrade to the new version – if you act fast! To get the upgrade, send an email to Jon Peltier, by Friday, June 13th (there is a contact link on the utility page).

If you haven’t bought this time-saving tool yet, the new Advanced Excel Charting Utility will be on sale today and tomorrow — June 12-13 — for the same price as the Standard utility. After that, the price goes up by $20.