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.

familytreeorgchart01

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.

townsendcode051

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.

familytreeorgchart02

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.

familytreeorgchart03

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.

 

_____________________

You may also like...

4 Responses

  1. Jon Peltier says:

    Here’s a topic for a future post: Conditional Compilation in VBA. It sounds freaky, but it’s quite simple. VBA contains a few high-level constants that refer to its environment, and you can use them to control what code is activated when VBA opens it. This lets you keep commands in your code that might cause compile errors on some computers.

    For example, to provide different code for Macs and Windows:

    #If Mac Then
    ‘ put mac code here

    #Else
    ‘ put windows code here

    #End If

    You can also use conditional compilation to provide different code for 32- and 64- bit Windows and/or Office and for newer and older versions of Excel.

    • Jeff Weir says:

      Awesome…I didn’t know about this option till now, Jon. Previously I just put the code that might not compile in a separate sub. This is much neater.

  2. Lee Townsend says:

    Thanks, Jon. I did not know about that option. I’ll look into it. Lee

  3. Keld Sørensen says:

    Family tree … I don’t get it !

    You can’t see who’s the father to which child … or do I get it wrong … then explain please !

Leave a Reply

Your email address will not be published. Required fields are marked *