Sunday, June 14, 2020

Excel Org Chart Creator

Create organizational charts in Excel with just one click using this Excel Org Chart Creator. Simple and easy to maintain with information in a table for names, tiers, and linkage to the structure. Try out the Excel Org Chart Creator and do not waste your precious time anymore updating diagrams for your team or department.


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