Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post Create an Org Chart/Vertical Hierarchy tree with VBA

    Hi all,

    I'd like to make a org chart/vertical tree that is automatically created using VBA at the push of a button, similar to the tree that was done by Maudible in this thread: http://windowssecrets.com/forums/sho...vertical-chart. And having a button to clear the chart so I can start fresh.

    Here is how my file is different...In my file there is a table that has drop down menus (to reduce the amount of data entry) in each cell. By selecting a level in one of the cells in the column named "Level" a list of names appear in the column named "Name". Selecting a different level in the "Level" column accesses a different list of names in the "Name" column. By populating the table with levels and names, I would like to make an vertical chart where Senior Managers are on top, Managers under Senior Managers, and Asst Managers under Managers. Regardless of the amount of Names at a certain level, their position on the chart should not change. I imagine that I would have to use the IF function to make this work but I am learning VBA and very much a novice. I included an example in the "Output Example" tab to show what I am looking for. But the way the chart was done by Maudible in the above link is exactly what I am trying to duplicate.

    Please Help!! See doc: Staffing Doc.xlsx

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Rush,

    Attached is your modified worksheet that builds a hierarchy tree based on the values in your Lavel and Name columns. The code assumes:

    1. There is a single position level above the Senior Manager. I used CEO to code it.

    2. The hierarchy is CEO > Senior Manager > Manager > Assistant Manager and there are no skipped in-between levels. Ex CEO > Senior Manager > Assistant Manager. If so, you will be warned and directed where the hierarchy is broken.

    3. You can have multiple levels Ex. CEO > Senior Manager > Manager1 > Manager2 > Assistant manager

    4. Titles (Levels) fall in the branch of the higher title above it. In the above example (#3), the Assistant Manager will be under Manager2 because of the order

    5. Levels have alternating colors: CEO (yellow), Senior Managers (green), Managers (yellow), Assistant Manager (green).

    HTH,
    Maud

    StaffingDoc1.png

    StaffingDoc2.png
    Attached Files Attached Files
    Last edited by Maudibe; 2016-02-19 at 17:21.

  3. #3
    New Lounger
    Join Date
    Feb 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maud! This is absolutely amazing and exactly what I wanted. Thank you. I hope I can get to the level where I can do this without needing help from forums. Again many many thanks

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •