Assigning Shape Layers from a List in Visio

A Visio user recently asked if it is possible to assign shapes to layers from a list. In his case, he has an Excel table which he has exported shapes and their text using Visio’s Shape Reports feature, to which he has added a column named Layer, and he wants to assign the shapes to these layers. In this article, I demonstrate how this can be done.

I decided to use my MVP Session Wheel diagram ( see ) for this example because it already has some layers assigned.

I created a new Shape Report called Presenter Shapes, where I filtered all shapes on the current page to those where the Presenter Shape Data row exists, and the Presenter actually has a value:


I then chose to only export the <Shape ID> and Presenter columns, ordered by <Shape ID>:


I then ran the report to export into a new Excel Workbook:


I named the worksheet tab Shape Layers, and I deleted the first row with the PresenterShapes title in it, and added another column, named Layer, with the following formula:

=MID(RC[-1], FIND(” “,RC[-1])+1,LEN(RC[-1])-FIND(” “,RC[-1]))

This is just so that I could quickly name some layer for each row.


I saved the workbook then I then, in Visio, used the Link Data to Shapes feature to add this table as External Data.


Finally, I had everything in place to write some VBA code.

First I created two support functions that could tell me if a specified shape or layer exits on a specified page. (It would be so useful if similar functions were already in the Visio Type Library).

Private Function LayerExists(ByVal pag As Visio.Page, ByVal layerName As String) As Boolean

Dim lyr As Visio.Layer
Dim exists As Boolean

    For Each lyr In pag.Layers
        If LCase(lyr.Name) = LCase(layerName) Then
            exists = True
            Exit For
        End If
    LayerExists = exists
End Function

Private Function ShapeExists(ByVal pag As Visio.Page, ByVal id As Integer) As Boolean

Dim shp As Visio.Shape
Dim exists As Boolean

    For Each shp In pag.Shapes
        If = id Then
            exists = True
            Exit For
        End If
    ShapeExists = exists
End Function

Next I wrote the main sub routine, AssignLayers(),  which requires the selected recordset in the External Data window to have two particular columns, Shape ID and Layer. It uses the data in these columns to assign the shapes to the layer, but of course, it must add the layer to the page, if it doesn’t exist already.

Also, remember that Visio shapes can be assigned to zero, one or many layers, so the routine also asks whether you want to replace any existing layer assignments or just to add to them.

Public Sub AssignLayers()

Dim win As Visio.Window
Dim drs As Visio.DataRecordset

    For Each win In ActiveWindow.Windows
        If = Visio.VisWinTypes.visWinIDExternalData Then
            Set drs = win.SelectedDataRecordset
            Exit For
        End If
    If drs Is Nothing Then
        MsgBox "There is no active DataRecordset", vbExclamation, "Assign Layers"
        Exit Sub
    End If

Dim iCol As Integer
Dim lyrColumn As Integer
Dim idColumn As Integer

    idColumn = -1
    lyrColumn = -1
    For iCol = 1 To drs.DataColumns.Count
        If drs.DataColumns.Item(iCol).Name = "Shape ID" Then
            idColumn = iCol - 1
        ElseIf drs.DataColumns.Item(iCol).Name = "Layer" Then
            lyrColumn = iCol - 1
        End If

    If idColumn = -1 Then
        MsgBox "There is no Shape ID column", vbExclamation, "Assign Layers"
        Exit Sub
    End If
    If lyrColumn = -1 Then
        MsgBox "There is no Layer column", vbExclamation, "Assign Layers"
        Exit Sub
    End If

Dim layerAction As Integer

    layerAction = MsgBox("Do you want to replace any existing layer assignments?" & _
        " (Select No to add the layer)", vbYesNoCancel, "Assign Layers")
    If layerAction = vbCancel Then
        Exit Sub
    End If

Dim rowIDs() As Long

    rowIDs = drs.GetDataRowIDs("")

Dim iRow As Integer
Dim data() As Variant
Dim lyrName As String
Dim shapeId As Integer
Dim shp As Visio.Shape
Dim lyr As Visio.Layer
Dim iLyr As Integer
Dim shapeOnLayer As Boolean

    For iRow = 0 To UBound(rowIDs)
        data = drs.GetRowData(rowIDs(iRow))
        shapeId = data(idColumn)
        lyrName = data(lyrColumn)
        If ShapeExists(ActivePage, shapeId) = True Then
            Set shp = ActivePage.Shapes.ItemFromID(shapeId)
            If LayerExists(ActivePage, lyrName) = False Then
                ActivePage.Layers.Add lyrName
            End If
            Set lyr = ActivePage.Layers.Item(lyrName)
            For iLyr = shp.LayerCount To 1 Step -1
                If LCase(shp.Layer(iLyr).Name) = LCase(lyrName) Then
                    shapeOnLayer = True
                    If layerAction = vbYes Then
                        ActivePage.Layers.Item(shp.Layer(iLyr).Name).Remove shp, 0
                    End If
                End If

            If shapeOnLayer = False Then
                lyr.Add shp, 0
            End If
        End If
End Sub

You may note that de-assigning and assigning shapes to layers in Visio is a little unintuitive because you have to get the page layer object and either remove or add the shape to it.

Also note that I have used zero as the second parameter to the layer.Add(…) and layer.Remove(…) methods. This ensures that the layer assignment is propagated to any sub-shapes.

The end result is that I can assign shapes to layers, and then I could switch off the visibility of Chris’s and Scott’s MVP sessions Smile !


Of course, I wouldn’t recommend that you do switch their sessions off, because they are all gems !


7 Responses to “Assigning Shape Layers from a List in Visio”

  1. risingson05 Says:

    Thanks for this (3 years later)! I had created a huge flowchart only to realize I need to change some specific shapes but forgot to assign layers. Would take forever to assign manually. However, two types of shapes that were previously grouped (which I ungrouped for this function) would not apply. Not sure why. If you’re still monitoring this maybe I could send you my file and you could take a look?

    • davidjpp Says:

      Sure, send me the file, or a OneDrive link, but I warn you that ungrouping a master instance is not normally a good idea….

      • risingson05 Says:

        Actually, I think I know what the deal was. These shapes that weren’t assigned were pulled from some Visio templates and I realized they had lots of custom formatting settings in the shapesheet. These were preventing me from even re-aligning the damn things. I got it sorted, so no worries. I highly doubt it was your code.

  2. Helvete Says:

    Hello, Interesting feature. I have a complex problem and perhaps could you give me some advice to solve it. I want to import data for different connectors between shapes. For each connector I have several values and I want to add each value to a specific layer.
    To be more pratical I want to create a flowsheet describing substances pathways between units so data are linked to connections between units. I want to create for each substance a specific layer and to add the values corresponding to one substance to the specific layer of the substance.
    Do you think it is possible to perform ? Thank you for your help

    • davidjpp Says:

      The Shape Data row visibility could be controlled by layer visibility, although one has to be mindful that layer indexes could be different on other pages.
      However, you are possibly thinking of the display of the values? If so, then you would need to turn your connectors into group shapes with multiple text blocks on different layers. Then their display can be controlled by layer visibility.

  3. Kev M Says:

    Hi David,

    I’ve recently found your website and keep coming back to it every time I google a visio related question, it’s such a great resource and you have a way of explaining things really clearly. This post looks to do exactly what I’m after, but I was wondering if you had a post somewhere you could link to that explains the starting steps of getting to the point I could enter this vba code as it’s not something i’m familiar with.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

Microsoft Office 365 Development, and more...


Automate, Accelerate, Accurate


Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Smart graphics for visual people

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: