Results 1 to 4 of 4
  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,754
    Thanks
    129
    Thanked 693 Times in 629 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 16: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

  4. #4
    New Lounger
    Join Date
    Mar 2017
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need your help

    Hey Maudibe,
    I know this is an old thread, hope you can still see this. Anyway I'm facing a similar but much simpler problem here.
    Basically my database contains 2 columns: Staff name and 'Report_to', I need to build an org chart using VBA, something similar to what you have wrote up there.
    Could you please help with this? Very much appreciated.

    Quote Originally Posted by Maudibe View Post
    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

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
  •