Macro To Size Shapes to Fit Fields
Would like to know the code for a macro to size all org. charts shapes across mulitple sheets to correctly fit the fields displayed in them.
Sometimes on the inital import from an excel document to Visio does this; however, in some cases the sizing is incorrect (eg. a hyphenated last name).
Sometimes on the inital import from an excel document to Visio does this; however, in some cases the sizing is incorrect (eg. a hyphenated last name).
Let me know whether this is close to what you're looking for. As you'll see in the comments, the code currently sets the shape width based on the text in the Title field. You can use a different field instead by making a change in the designated place, if you'd like. Or I can adjust the code to set the width to match the longest of several fields if that's useful.
Scott
Scott
Sub Resize()
' code scans all shapes on all pages looking for ones that contain a field called User.ShapeType,
' which is used by the org chart software to store each shape's type; types 0-6 are the ones
' we want to adjust
'
' code currently sets width based on the employee's title; to adjust based on a different
' field, replace "Title" in "TEXTWIDTH(Prop.Title)" with the name of the other field
Dim pg As Page
Dim shp As Shape
For Each pg In ActiveDocument.Pages
For Each shp In pg.Shapes
' restrict to those shapes that have a type and type is <= 6
If shp.CellExists("User.ShapeType", False) Then
If shp.Cells("User.ShapeType").Formula <= 6 Then
If Not shp.CellExists("User.DesiredWidth", False) Then _
shp.AddNamedRow visSectionUser, "DesiredWidth", visTagDefault
' set field name here VVVVV
shp.Cells("User.DesiredWidth").Formula = "TEXTWIDTH(Prop.Title)"
shp.Cells("Width").Formula = shp.Cells("User.DesiredWidth").ResultIU
End If
End If
Next
' invoke layout function to re-layout page
pg.Layout
Next
End Sub
ASKER
Interesting approach; sorry I should have been more specific; I'm more interested in keeping the width accross all shapes fixed and adjusting the height to accomodate the fields plus a a pixel or so of padding.... does the VB language exist for this?
You can control the vertical and horizontal spacing between shapes without needing code. Just click Organization Chart>Change Spacing...>Custom>Values and you can set whatever you'd like.
As far as the height and width of shapes is concerned, the macro above can be modified pretty easily. In the default shapes, Executive is wider than Manager and Position. Would you like different values for each type? What should the widths be?
As far as the height and width of shapes is concerned, the macro above can be modified pretty easily. In the default shapes, Executive is wider than Manager and Position. Would you like different values for each type? What should the widths be?
ASKER
I would like all shapes to match the width on Manager and/or Position. When I said "padding" I only meant when height auto-sizes to leave a pixel or so of space between the border of the shape and the fields.
It seems the problem on import/generate is that it just sometimes doesnt' make the height tall enough on some shapes....
It seems the problem on import/generate is that it just sometimes doesnt' make the height tall enough on some shapes....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works.... very well. Almost too well, I didn't realize that I would have so many shapes that look so short; anyway to set a stipulation that says to do all that but make the shapes height no less than .5626''; if not thank you and i'll reward points.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks scott, you're always extremely helpful.
Regards,
Scott