There are many tools available to create charts and diagrams such
as Visio and some other Microsoft products, for example. However, if you have
used those you know it takes some time, especially when there is a change to
the chart and you need to add, delete or rearrange boxes.
You can avoid all that using the Excel Org Chart Creator, which
just requires a list of resources with their respective tier and linkage in the
organization or department.
Team Structure
The team structure in Excel Org Chart Creator is a list of
resources with title and name, followed by the tier and linkage to the resource
ID. It is important to create that list it in the right order, but
nevertheless, the macro checks the order and warns if not done correctly.
Each row corresponds to a box in the chart. The macro loops
through the rows and keeps adding shapes accordingly. Excel Org Chart Creator allows
to add a maximum of 25 resources and it is not advisable to have more than 4 or
5 tiers. But if you have a bigger team, just try it out and see what happens.
The linkage between resources is done through the ID number (not
the row number). Resources must link to one tier above only.
Adding Shapes
Excel Org Chart Creator works with the VBA Shapes object, and
specifically adds rectangular shapes with the ‘Add’ method followed by
attributes for the position and size of the shape. To do so, we have added a
class module (clsOrgBox) that creates an instance of that shape object
(myOrgBox). The AddOrgBox procedure is called to add each box shape in the
chart.
Dim
myOrgBox As Object
Set
myOrgBox = New clsOrgBox
myOrgBox.AddOrgBox
The boxes are added by tier and following the linkage indicated
in the table. Excel Org Chart Creator adds the necessary spacing when placing
the boxes and it auto fits the length of each box.
Here’s how to add a rectangular shape fitting to a particular
cell in the Chart worksheet.
Set
newOrgBox = orgChart.Shapes.AddShape(msoShapeRoundedRectangle,
clLeft,
clTop, 80, 36)
Where clLeft and clTop are the x and y coordinates in the worksheet,
80 is the width and 36 is the height of the shape. The width can change later
when using the AutoFit method depending on the length of name and title.
The values of clLeft and clTop are linked to a particular cell
defined as an object as follows:
Set cl =
orgChart.Cells(f, c)
clLeft =
cl.Left + 4
clTop =
cl.Top + 2
Then we use the TextFrame.Characters properties of the Shapes
object to add text, color, and other formatting to the box shape.
Adding Lines and Chart Title
Once all shapes have been added, Excel Org Chart Creator adds elbow-type
connecting lines between the shapes. Here’s how we add the connector to a
shape.
orgChart.Shapes.AddConnector(msoConnectorElbow,
10, 10, 10, 10).Select
Selection.ShapeRange.ConnectorFormat.BeginConnect
ActiveSheet.Shapes(fromBox), 3
Selection.ShapeRange.ConnectorFormat.EndConnect
ActiveSheet.Shapes(toBox),
1
Where fromBox and toBox are the name of the two boxes to be
connected. The connector begins at the bottom of the fromBox (position 3) and
ends at the top of toBox (position 1).
The coordinates of the connector shape (always 10 in this
example) are not relevant as the selected connector is moved and resized when
linking the two rectangular shapes.
Finally it adds a title to the chart (see the AddTitle procedure
in the Chart module) that can be renamed as needed.
Click Here to download
Excel Org Chart Creator.
No comments:
Post a Comment