A Visio user recently asked how to add a salary amount for an Org Chart person automatically, based on their selected Level. I thought it was a bit long to answer in the forum, so I have written my answer below. It demonstrates how you can have your own custom template that works with the Organization Chart add-on in Visio.
My approach to creating a custom template that still uses the Organization Chart Add-On is to create a new drawing from the built-in template, and then to add extra properties to it.
First, select Show Document Stencil to view all of the local copies of the built-in master shapes. If they are not there, then simply drag and drop them from the built-in stencil on to the first page, and then delete the shapes on the page. This will not delete the local copies of the masters.
You should check that the Match master by name on drop property is ticked for each master in the Document Stencil, otherwise the Org Chart Add-On may decide to use the original Organization Chart Shapes stencil master with the same name.
Open the ShapeSheet of the Document by using the Show ShapeSheet drop-down menu on the Developer tab.
Add two extra rows to the User-defined Cells section called LevelList and SalaryList, with the Value formulas:
Note that the salaries are entered as numbers without the currency symbol.
Now, each of the relevant master shapes need to be updated with the new properties.
So, open up the Executive master shape in edit mode, and add two new Shape Data rows (Level and Salary), and then one new User-defined cell (SalaryTrigger):
The Prop.Level Shape Data row has type formula =GUARD(1) because it is a fixed list, the Format formula is =TheDoc!User.LevelList , and the Value formula was created by selecting Vacant from the Shape Data window.
The Prop.Salary Shape Data row has type formula =GUARD(7) because it is a currency, and the Value formula will be created by selecting any Level value from the Shape Data window.
The SalaryTrigger User-defined cell Value formula is:
This will update the value of the Salary cell when a new Level is selected, and, moreover, the value will change if you update the User.SalaryList values in the Document ShapeSheet.
The above trigger formula will allow for local editing of the salary on individual shapes, but if you want to make the salary read-only then insert the GUARD() function as below:
Now, you can save your Executive master, and it will have the updated functionality. However, there are seven almost identical masters for Position Type in the Organization Chart stencil, and each of these need to be updated identically!
Now, you can manually make all of the changes to the other six Position Type masters (Manager, Position, Consultant, Vacancy, Assistant, and Staff) , or you could use the VBA code below to make the changes for you (note that this has been updated from my original post):
Option Explicit Public Sub UpdatePositionTypeMastersFromExcecutive() Dim mstExec As Visio.Master Dim mst As Visio.Master Dim mstCopy As Visio.Master Dim shpExec As Visio.Shape Dim shpCopy As Visio.Shape 'Get the Executive master For Each mst In ActiveDocument.Masters If mst.Name = "Executive" Then Set mstExec = mst Set shpExec = mstExec.Shapes(1) Exit For End If Next mst 'Abort if not found If mstExec Is Nothing Then Exit Sub Dim iRow As Integer Dim iColumn As Integer 'Loop through each master 'and filter to include those with the Prop.Name Shape Data row For Each mst In ActiveDocument.Masters If Not mst.Name = "Executive" _ And mst.Shapes(1).CellExists("Prop.Name", Visio.visExistsAnywhere) Then Debug.Print "Updating " & mst.Name Set mstCopy = mst.Open 'Open the master for editing Set shpCopy = mstCopy.Shapes(1) 'Get the group shape 'Loop through Shape Data rows to find extra ones For iRow = 0 To shpExec.RowCount(visSectionProp) - 1 If shpCopy.CellsSRCExists(visSectionProp, iRow, 0, visExistsAnywhere) = 0 Then 'Add the row Debug.Print , "Adding " & shpExec.CellsSRC(visSectionProp, iRow, 0).rowName shpCopy.AddNamedRow visSectionProp, shpExec.CellsSRC(visSectionProp, iRow, 0).rowName, 0 For iColumn = 0 To shpExec.RowsCellCount(visSectionProp, iRow) - 1 If Len(shpExec.CellsSRC(visSectionProp, iRow, iColumn).Formula) > 2 Then 'Add the formulas Debug.Print , , iColumn, shpExec.CellsSRC(visSectionProp, iRow, iColumn).Formula shpCopy.CellsSRC(visSectionProp, iRow, iColumn).FormulaU = _ shpExec.CellsSRC(visSectionProp, iRow, iColumn).FormulaU End If Next iColumn End If Next 'Loop through User-defined cells rows to find extra ones Dim rowName As String Dim newRow As Integer For iRow = 0 To shpExec.RowCount(visSectionUser) - 1 rowName = shpExec.CellsSRC(visSectionUser, iRow, 0).rowName If shpCopy.CellExists("User." & rowName, visExistsAnywhere) = 0 Then 'Add the row Debug.Print , "Adding " & shpExec.CellsSRC(visSectionUser, iRow, 0).rowName newRow = shpCopy.AddNamedRow(visSectionUser, shpExec.CellsSRC(visSectionUser, iRow, 0).rowName, 0) For iColumn = 0 To shpExec.RowsCellCount(visSectionUser, iRow) - 1 If Len(shpExec.CellsSRC(visSectionUser, iRow, iColumn).Formula) > 2 Then 'Add the formulas Debug.Print , , iColumn, shpExec.CellsSRC(visSectionUser, iRow, iColumn).Formula shpCopy.CellsSRC(visSectionUser, newRow, iColumn).FormulaU = _ shpExec.CellsSRC(visSectionUser, iRow, iColumn).FormulaU End If Next iColumn End If Next mstCopy.Close End If Next mst End Sub
All you need to do now is save your drawing as a Visio template, and you are ready to create Organization Charts from your new template!
Addendum (how to write a report to total the Salary by Level):
If you copying the XML below into a file called TotalSalaryCost.vrd (for example) using Notepad, then run Shape Reports, and then browse for the file, andRun as a Visio Shape …. you should get an embedded Excel worksheet that sums up the salary cost for each level, and get an overall total :
<?xml version="1.0" encoding="utf-8"?> <VisioReportDefinition xmlns="urn:schemas-microsoft-com:office:visio:reportdefinition" ReportExists="1" SelectDrawingDataMethod="1" Description="" Precision="0" ShowUnits="1" CaseSensitive="0" UserCells="0" HiddenProperties="0" AdvancedFilter="0" FilterExpression="" Version="3"> <Name>Total Salary Cost</Name> <Title>TotalCost</Title> <VisioRptDefField ID="1" Display="1" DisplayOrder="1" Type="0" SummaryTypes="1" Tag="2"> <Name>Level</Name> <DisplayName>Level</DisplayName> </VisioRptDefField> <VisioRptDefField ID="2" Display="1" DisplayOrder="2" Type="7" SummaryTypes="2" Tag="2"> <Name>Salary</Name> <DisplayName>Salary</DisplayName> </VisioRptDefField> <VisioRptDefGroup ID="0" GroupingField="1" ItemDisplay="1" GrandSummary="1" ExcludeDuplicates="0"/> </VisioReportDefinition>