radiantjoy
asked on
Possible to automatically Arrange Subordinates (re-layout) Visio 2003 org chart shapes in the Org Chart stencil
Is it possible to change the org chart's default layout to a vertical layout using the macros created by an EE super guru expert?
Below are examples of the end layout I'd like to achieve.
Thank you!
In previous questions, a macros converted a list of hyperlinks into an org chart then changed the standard org chart shapes into custom shapes.
https://www.experts-exchange.com/questions/26720393/Assign-hyperlinks-from-Excel-2003-to-Visio-2003-shapes.html
https://www.experts-exchange.com/questions/26727946/Changing-all-Visio-2003-shapes-to-another-template's-shapes.html
https://www.experts-exchange.com/questions/26830146/Replacing-Visio-2003-org-chart-shapes-in-the-Org-Chart-stencil.html
Replace-Org-Chart-Shapes-v04-1-.vsd Replace-Org-Chart-Shapes-v04-1-.vsd org-chart-after-macros-v04.vsd
Below are examples of the end layout I'd like to achieve.
Thank you!
In previous questions, a macros converted a list of hyperlinks into an org chart then changed the standard org chart shapes into custom shapes.
https://www.experts-exchange.com/questions/26720393/Assign-hyperlinks-from-Excel-2003-to-Visio-2003-shapes.html
https://www.experts-exchange.com/questions/26727946/Changing-all-Visio-2003-shapes-to-another-template's-shapes.html
https://www.experts-exchange.com/questions/26830146/Replacing-Visio-2003-org-chart-shapes-in-the-Org-Chart-stencil.html
Replace-Org-Chart-Shapes-v04-1-.vsd Replace-Org-Chart-Shapes-v04-1-.vsd org-chart-after-macros-v04.vsd
Moderators: please delete my previous post, it had errors. There doesn't seem to be a way for me to edit my posts.
I've gone through Visio 2007's orgchart menus and toolbars and compiled a list of the add-ons and arguments that are connected with each menu item and toolbar button.
So for example, if a shape is selected in the drawing window, you could execute a layout command for stacking subordinates vertically like this:
Call visio.Application.Addons("
Here's the list:
Org Chart Menu Items
---------------------
Caption: Re-&layout
Add-on: OrgC11
Add-on args: /relayout
Caption: Best &Fit to Page
Add-on: OrgC11
Add-on args: /fittopage
Caption: A&rrange Subordinates...
Add-on: OrgC11
Add-on args: /layoutdlg
Caption: Hide Su&bordinates
Add-on: OrgC11
Add-on args: /hidesubordinates
Caption: &Move Subordinates
Add-on:
Add-on args:
Caption: Change &Position Type...
Add-on: OrgC11
Add-on args: /convertshapes
Caption: &Synchronize
Add-on:
Add-on args:
Caption: &Import Organization Data...
Add-on: OrgCWiz
Add-on args: /AddToOldDrawing
Caption: E&xport Organization Data...
Add-on: OrgC11
Add-on args: /exportorgdata
Caption: &Compare Organization Data...
Add-on: OrgC11
Add-on args: /diffcompare
Caption: C&hange Spacing...
Add-on: OrgC11
Add-on args: /editalprops
Caption: &Options...
Add-on: OrgC11
Add-on args: /editoverallprops
Org Chart Toolbar Items
---------------------
Caption: Re-layout
Add-on: OrgC11
Add-on args: /relayout
Caption: Horizontal Layout (6 items)
0
Caption: Center
Add-on: OrgC11
Add-on args: /toolbar_horiz1
1
Caption: Align Left
Add-on: OrgC11
Add-on args: /toolbar_horiz2
2
Caption: Align Right
Add-on: OrgC11
Add-on args: /toolbar_horiz3
3
Caption: Stagger
Add-on: OrgC11
Add-on args: /toolbar_horiz6
Caption: Vertical Layout (6 items)
0
Caption: Align Left
Add-on: OrgC11
Add-on args: /toolbar_vert1
1
Caption: Align Right
Add-on: OrgC11
Add-on args: /toolbar_vert3
2
Caption: Staff Align Left
Add-on: OrgC11
Add-on args: /toolbar_vert5
3
Caption: Staff Align Right
Add-on: OrgC11
Add-on args: /toolbar_vert6
Caption: Side By Side (6 items)
0
Caption: Single Side
Add-on: OrgC11
Add-on args: /toolbar_sidebyside2
1
Caption: Single Top
Add-on: OrgC11
Add-on args: /toolbar_sidebyside1
2
Caption: Double Side
Add-on: OrgC11
Add-on args: /toolbar_sidebyside4
3
Caption: Double Top
Add-on: OrgC11
Add-on args: /toolbar_sidebyside3
Caption: Move Left
Add-on: OrgC11
Add-on args: /shiftleft
Caption: Move Right
Add-on: OrgC11
Add-on args: /shiftright
I've gone through Visio 2007's orgchart menus and toolbars and compiled a list of the add-ons and arguments that are connected with each menu item and toolbar button.
So for example, if a shape is selected in the drawing window, you could execute a layout command for stacking subordinates vertically like this:
Call visio.Application.Addons("
Here's the list:
Org Chart Menu Items
---------------------
Caption: Re-&layout
Add-on: OrgC11
Add-on args: /relayout
Caption: Best &Fit to Page
Add-on: OrgC11
Add-on args: /fittopage
Caption: A&rrange Subordinates...
Add-on: OrgC11
Add-on args: /layoutdlg
Caption: Hide Su&bordinates
Add-on: OrgC11
Add-on args: /hidesubordinates
Caption: &Move Subordinates
Add-on:
Add-on args:
Caption: Change &Position Type...
Add-on: OrgC11
Add-on args: /convertshapes
Caption: &Synchronize
Add-on:
Add-on args:
Caption: &Import Organization Data...
Add-on: OrgCWiz
Add-on args: /AddToOldDrawing
Caption: E&xport Organization Data...
Add-on: OrgC11
Add-on args: /exportorgdata
Caption: &Compare Organization Data...
Add-on: OrgC11
Add-on args: /diffcompare
Caption: C&hange Spacing...
Add-on: OrgC11
Add-on args: /editalprops
Caption: &Options...
Add-on: OrgC11
Add-on args: /editoverallprops
Org Chart Toolbar Items
---------------------
Caption: Re-layout
Add-on: OrgC11
Add-on args: /relayout
Caption: Horizontal Layout (6 items)
0
Caption: Center
Add-on: OrgC11
Add-on args: /toolbar_horiz1
1
Caption: Align Left
Add-on: OrgC11
Add-on args: /toolbar_horiz2
2
Caption: Align Right
Add-on: OrgC11
Add-on args: /toolbar_horiz3
3
Caption: Stagger
Add-on: OrgC11
Add-on args: /toolbar_horiz6
Caption: Vertical Layout (6 items)
0
Caption: Align Left
Add-on: OrgC11
Add-on args: /toolbar_vert1
1
Caption: Align Right
Add-on: OrgC11
Add-on args: /toolbar_vert3
2
Caption: Staff Align Left
Add-on: OrgC11
Add-on args: /toolbar_vert5
3
Caption: Staff Align Right
Add-on: OrgC11
Add-on args: /toolbar_vert6
Caption: Side By Side (6 items)
0
Caption: Single Side
Add-on: OrgC11
Add-on args: /toolbar_sidebyside2
1
Caption: Single Top
Add-on: OrgC11
Add-on args: /toolbar_sidebyside1
2
Caption: Double Side
Add-on: OrgC11
Add-on args: /toolbar_sidebyside4
3
Caption: Double Top
Add-on: OrgC11
Add-on args: /toolbar_sidebyside3
Caption: Move Left
Add-on: OrgC11
Add-on args: /shiftleft
Caption: Move Right
Add-on: OrgC11
Add-on args: /shiftright
Nice, Chris, thanks. I had taken a more brute force approach using SendKeys, but have changed the code to use the appropriate command. Which, by the way, turns out to be one that's not on your list, /toolbar_vert2, based on the style that Joy is looking for.
Org-chart-with-station-shapes-v0.zip
Org-chart-with-station-shapes-v0.zip
One more thing, Joy -- currently, the new code only arranges the position shapes under the managers. If you want the first-level shapes under the executives to be arranged in a particular way, just let me know.
Scott
Scott
ASKER
Thank you, Scott! This looks great but I do have a couple of questions:
1. Some of the shapes below the Executive appear as shaded circles - you had been kind enough to change that in v4 of the macros, but it's appearing again. I did a side-by-side comparison of v4 and v5 and the only difference i saw was that this code was commented out (shpTo.CellsU("LinePattern
2. The formatting applies beautifully to some managers and employee shapes but not to others (Page 2 1.4 and below are tweaked). What did I do wrong?
I'm attaching the before vsd (a drawing based on the converted Excel data), the after vsd and the replace macros.
Thank you!!!! test-prototype-before.vsd Test-prototype-after.vsd
Replace-Org-Chart-Shapes-v05.vsd
1. Some of the shapes below the Executive appear as shaded circles - you had been kind enough to change that in v4 of the macros, but it's appearing again. I did a side-by-side comparison of v4 and v5 and the only difference i saw was that this code was commented out (shpTo.CellsU("LinePattern
2. The formatting applies beautifully to some managers and employee shapes but not to others (Page 2 1.4 and below are tweaked). What did I do wrong?
I'm attaching the before vsd (a drawing based on the converted Excel data), the after vsd and the replace macros.
Thank you!!!! test-prototype-before.vsd Test-prototype-after.vsd
Replace-Org-Chart-Shapes-v05.vsd
Re #1: My fault -- I forgot to mention that you need to replace the stencil I previously sent (org chart station shapes.vss) with the one in the zip file above. The new layout function required that I alter the manager shape in the stencil, hence the new version.
#2: I have no idea! Similarly, I have no idea why the connector for 7.9 on page-1 follows a different path than every other line from the executive shape. As best I can tell, neither of these things is the result of anything you or my code is doing -- they're just quirks of the org chart add-in (of which there are many).
And speaking of quirks... I can't recall whether we've discussed this before, but one of the *long standing* oddities of the org chart add-in is that you have no control over the left-to-right sequence in which it places subordinates. That's why on Page-1 you see 7.11, 7.7, 7.14, 7.17... There is neither rhyme nor reason and there's nothing you can do. We Visio MVPs have complained loudly and often; Chris, aka Visio_Guy, who responded above has been an MVP a lot longer than I and can vouch for the number of conversations about this peculiar "feature."
#2: I have no idea! Similarly, I have no idea why the connector for 7.9 on page-1 follows a different path than every other line from the executive shape. As best I can tell, neither of these things is the result of anything you or my code is doing -- they're just quirks of the org chart add-in (of which there are many).
And speaking of quirks... I can't recall whether we've discussed this before, but one of the *long standing* oddities of the org chart add-in is that you have no control over the left-to-right sequence in which it places subordinates. That's why on Page-1 you see 7.11, 7.7, 7.14, 7.17... There is neither rhyme nor reason and there's nothing you can do. We Visio MVPs have complained loudly and often; Chris, aka Visio_Guy, who responded above has been an MVP a lot longer than I and can vouch for the number of conversations about this peculiar "feature."
ASKER
Hi Scott!
I replaced the stencil shapes but now I freeze when I run the macros. Oh, I am so sorry but can you tell me what I'm doing wrong?
[As a complete aside, when I import shapes from the Excel file with macros, only the first level (executive appears) now appears...I didn't have any issues before running the change macros - I know this is a shot in the dark and I will do more troubleshooting if the issue goes away with the below, but do you think the two might be related? In actuality, my .vsd may go as far as 5 levels deep.]
Thank you!!
test-prototype-before.vsd Org-chart-station-shapes.vss Replace-Org-Chart-Shapes-v05.vsd
I replaced the stencil shapes but now I freeze when I run the macros. Oh, I am so sorry but can you tell me what I'm doing wrong?
[As a complete aside, when I import shapes from the Excel file with macros, only the first level (executive appears) now appears...I didn't have any issues before running the change macros - I know this is a shot in the dark and I will do more troubleshooting if the issue goes away with the below, but do you think the two might be related? In actuality, my .vsd may go as far as 5 levels deep.]
Thank you!!
test-prototype-before.vsd Org-chart-station-shapes.vss Replace-Org-Chart-Shapes-v05.vsd
Joy,
Are you sure Visio actually hangs on your PC? How long did you wait before deciding it was hung?
For example, on my PC it takes one minute 20 seconds for the shape replacement to complete using your sample org chart. There is no visible sign of activity during that period, but it does finally finish. Please try again and let me know what happens.
Regarding the import issue, I have to assume that's a separate problem because you don't run the substitution macro until after the org chart wizard has finished doing the initial import. It sounds like there might be a problem with structure or format of the data in the Excel workbook. Perhaps there is an issue with the functions/macros we wrote months ago to prepare your data? Let's get the shape substitution/layout working properly then I'll be happy to take a look at the import problem.
Scott
Are you sure Visio actually hangs on your PC? How long did you wait before deciding it was hung?
For example, on my PC it takes one minute 20 seconds for the shape replacement to complete using your sample org chart. There is no visible sign of activity during that period, but it does finally finish. Please try again and let me know what happens.
Regarding the import issue, I have to assume that's a separate problem because you don't run the substitution macro until after the org chart wizard has finished doing the initial import. It sounds like there might be a problem with structure or format of the data in the Excel workbook. Perhaps there is an issue with the functions/macros we wrote months ago to prepare your data? Let's get the shape substitution/layout working properly then I'll be happy to take a look at the import problem.
Scott
ASKER
Forgive me, when I tried this earlier in the week, it hung for 15 minutes (I started it and then went about other tasks) before I gave up.
I tried again today and it worked within a couple of minutes. Apologize for the false alarm.
Thank you for letting me know that it's a Visio quirk about the non-sequential ordering. I'll move the shapes around manually to address that.
To minimize the manual tweaking, may I impose on you one last time to see if there is a way that I can get all of the lines to appear straight? Some are still askew and I'm not sure how to address it.
THANK YOU!
test-prototype-after.vsd
I tried again today and it worked within a couple of minutes. Apologize for the false alarm.
Thank you for letting me know that it's a Visio quirk about the non-sequential ordering. I'll move the shapes around manually to address that.
To minimize the manual tweaking, may I impose on you one last time to see if there is a way that I can get all of the lines to appear straight? Some are still askew and I'm not sure how to address it.
THANK YOU!
test-prototype-after.vsd
I will look into the placement of the Manager shapes under the Executive. It seems to be one near the executive on each page that isn't properly aligned...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The specific value I chose for horizontal spacing was totally arbitrary -- 1 1/8 inches between shapes. If you want a larger or smaller interval, let me know.
ASKER
Amazing! Thank you so much, Scott! Perfect!
Thank you for sharing that the "hanging" happened to you as well. Often, as users/un-experts, we feel that it's us who cause any funkiness. You are so gracious to be so honest.
I wish I could award you more than the maximum amount of points but I hope my immense gratitude will supplements the points awarded.
Thank you, again, for everything!
Joy
Thank you for sharing that the "hanging" happened to you as well. Often, as users/un-experts, we feel that it's us who cause any funkiness. You are so gracious to be so honest.
I wish I could award you more than the maximum amount of points but I hope my immense gratitude will supplements the points awarded.
Thank you, again, for everything!
Joy
ASKER
Scott was immensely patient and extremely generous with his time, knowledge, and good-will.
His patience is never-ending and helped me with a solution that exceeded my expectations.
I am most grateful.
His patience is never-ending and helped me with a solution that exceeded my expectations.
I am most grateful.
Joy,
You are most welcome. You deserve a special award for your patience during the months that it's taken me to work on this sporadically. I'm glad the result is something you can use. Re your desire to award more than the allotted points -- I appreciate the sentiment. You can, of course, tell all of your friends to buy my Visio book! :-)>
BTW, I vaguely remember that the station shapes in your very earliest prototype showed colored rings for the three position types. Let me know if you want to add color to the masters for the three position shapes. It's easy to do and I can send you the steps.
Also, one other enhancement that occurred to me as I was finishing up over the weekend: would you like the size of each Visio page to expand automatically to accommodate all of the shapes?
Regards,
Scott
You are most welcome. You deserve a special award for your patience during the months that it's taken me to work on this sporadically. I'm glad the result is something you can use. Re your desire to award more than the allotted points -- I appreciate the sentiment. You can, of course, tell all of your friends to buy my Visio book! :-)>
BTW, I vaguely remember that the station shapes in your very earliest prototype showed colored rings for the three position types. Let me know if you want to add color to the masters for the three position shapes. It's easy to do and I can send you the steps.
Also, one other enhancement that occurred to me as I was finishing up over the weekend: would you like the size of each Visio page to expand automatically to accommodate all of the shapes?
Regards,
Scott
ASKER
Funny that you mentioned your book - I already bought the Kindle version!! And, believe me, I have been speaking about you to all of my colleagues...they are most impressed that someone as knowledgeable as you took the time to help little 'ol me!
I like the two-tone end result we came up with...the indentation is perfect to differentiate the different levels.
By having Visio automatically accommodate all of the shapes, do you mean, changing from Letter to Legal to supersize BIG page size? Or, squish stuff so that it fits in a letter size page.
If the latter is that possible? if so, I'll open a new request! It would be great to automate...I was thinking about having to manually smoosh the larger pages somehow.
Thank you!
I like the two-tone end result we came up with...the indentation is perfect to differentiate the different levels.
By having Visio automatically accommodate all of the shapes, do you mean, changing from Letter to Legal to supersize BIG page size? Or, squish stuff so that it fits in a letter size page.
If the latter is that possible? if so, I'll open a new request! It would be great to automate...I was thinking about having to manually smoosh the larger pages somehow.
Thank you!
I've gone through Visio 2007's orgchart menus and toolbars and compiled a list of the add-ons and arguments that are connected with each menu item and toolbar button.
So for example, if a shape is selected in the drawing window, you could execute a layout command for stacking subordinates vertically like this:
Call visio.Application.Addons("
Here's the list:
Org Chart Menu Items
---------------------
Caption: Re-&layout
Add-on: OrgC11
Add-on args: /relayout
Caption: Best &Fit to Page
Add-on: OrgC11
Add-on args: /fittopage
Caption: A&rrange Subordinates...
Add-on: OrgC11
Add-on args: /layoutdlg
Caption: Hide Su&bordinates
Add-on: OrgC11
Add-on args: /hidesubordinates
Caption: &Move Subordinates
Add-on:
Add-on args:
Caption: Change &Position Type...
Add-on: OrgC11
Add-on args: /convertshapes
Caption: &Synchronize
Add-on:
Add-on args:
Caption: &Import Organization Data...
Add-on: OrgCWiz
Add-on args: /AddToOldDrawing
Caption: E&xport Organization Data...
Add-on: OrgC11
Add-on args: /exportorgdata
Caption: &Compare Organization Data...
Add-on: OrgC11
Add-on args: /diffcompare
Caption: C&hange Spacing...
Add-on: OrgC11
Add-on args: /editalprops
Caption: &Options...
Add-on: OrgC11
Add-on args: /editoverallprops
Org Chart Toolbar Items
---------------------
Caption: Re-layout
Add-on: OrgC11
Add-on args: /relayout
Caption: Horizontal Layout (6 items)
Caption: Center
Add-on: OrgC11
Add-on args: /toolbar_horiz1
Caption: Center
Add-on: OrgC11
Add-on args: /toolbar_horiz1
Caption: Center
Add-on: OrgC11
Add-on args: /toolbar_horiz1
Caption: Center
Add-on: OrgC11
Add-on args: /toolbar_horiz1
Caption: Center
Add-on: OrgC11
Add-on args: /toolbar_horiz1
Caption: Center
Add-on: OrgC11
Add-on args: /toolbar_horiz1
Caption: Vertical Layout (6 items)
Caption: Align Left
Add-on: OrgC11
Add-on args: /toolbar_vert1
Caption: Align Left
Add-on: OrgC11
Add-on args: /toolbar_vert1
Caption: Align Left
Add-on: OrgC11
Add-on args: /toolbar_vert1
Caption: Align Left
Add-on: OrgC11
Add-on args: /toolbar_vert1
Caption: Align Left
Add-on: OrgC11
Add-on args: /toolbar_vert1
Caption: Align Left
Add-on: OrgC11
Add-on args: /toolbar_vert1
Caption: Side By Side (6 items)
Caption: Single Side
Add-on: OrgC11
Add-on args: /toolbar_sidebyside2
Caption: Single Side
Add-on: OrgC11
Add-on args: /toolbar_sidebyside2
Caption: Single Side
Add-on: OrgC11
Add-on args: /toolbar_sidebyside2
Caption: Single Side
Add-on: OrgC11
Add-on args: /toolbar_sidebyside2
Caption: Single Side
Add-on: OrgC11
Add-on args: /toolbar_sidebyside2
Caption: Single Side
Add-on: OrgC11
Add-on args: /toolbar_sidebyside2
Caption: Move Left
Add-on: OrgC11
Add-on args: /shiftleft
Caption: Move Right
Add-on: OrgC11
Add-on args: /shiftright