• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

bVisual

  • Home
  • Services
    • How Visio smartness can help your business
    • Visio visual in Power BI
    • Visio Consulting Services
    • Visio Bureau Services
    • Visio Training and Support Services
  • Products
    • Visio Shape Report Converter
    • SS Plus
    • LayerManager
    • visViewer
    • Metro Icons
    • Rules Tools for Visio
    • The Visio 2010 Sessions App
    • Multi-Language Text for Visio
    • Document Imager for Visio
    • multiSelect for Visio
    • pdSelect for Visio
  • Case Studies
    • Case studies overview
    • Visualizing Construction Project Schedules
    • Visio Online Business Process Mapping
    • Nexans Visio Template
    • CNEE Projects, WorldCom
    • Chase Manhattan Bank
  • News
    • Recent news
    • News archive
  • Resources
    • Articles➡
      • ShapeSheet Functions A-Z
      • Comparing Visio for the Web and Desktop
      • Customising Visio Shapes for the Web App
      • Key differences between the Visio desktop and web apps
      • Using the Visio Data Visualizer in Excel
      • Using Visio in Teams
      • Creating Visio Tabs and Apps for Teams with SharePoint Framework (SPFx)
      • Designing Power Automate Flows with Microsoft Visio
      • Innovative uses of Visio Lists
    • Webcasts ➡
      • Visio in Organizations
      • My session and other Visio sessions at MSIgnite 2019
      • Power up your Visio diagrams
      • Vision up your Visio diagrams
      • The Visio 2010 MVP Sessions
    • Visio Web Learning Resources
    • Books➡
      • Mastering Data Visualization with Microsoft Visio
      • Microsoft Visio Business Process Diagramming and Validation
      • Visualizing Information with Microsoft Visio
  • Blog
    • Browse blog articles
    • Visio Power BI articles
    • Visio for Web articles
    • A history of messaging and encryption
  • About us
    • About bVisual
    • Testimonials
    • Bio of David Parker
    • Contact Us
    • Website Privacy Policy
    • Website terms and conditions
    • Ariba Network
You are here: Home / Visio / Which field is that cow in?

Published on March 21, 2011 by David Parker

Which field is that cow in?

I reader of one of my earlier posts, Copy Data from one Shape to Another ( see http://davidjpp.wordpress.com/2009/02/02/copying-data-from-one-shape-to-another/ ) asked how a cow can inherit the field number of from the field that it in. I think that this is just an example of how you can automatically update any Shape Data value from an underlying container, but I will use a cow in a field example anyway. This example uses VBA code, but should be good to use in any version of Visio since Visio 2000.

I created a field shape, actually just a filled green rectangle with a brown dash line, with a single Shape Data row, Prop.FieldName. I also created a cow shape from Clip Art, and added two Shape Data rows, Prop.Name and Prop.InField. I also added an optional third Shape Data row, Prop.Quadrant, to add extra precision, if required.

So that you can easily see what the value is in the Prop.Name and Prop.InField rows, I inserted a custom formula into the text of the Cow shape:

=Prop.Name & IF(STRSAME(Prop.InField,””),” is not in a field”,” is in quadrant ” & Prop.Quadrant & ” of the “& Prop.InField & ” field”)

Thus, you can see which field the cow is in, and even which part of the field.

image

Obviously, you can type in the name (and quadrant) of the field that the cow is in, but you want it to update automatically whenever a cow is dropped into a field; moved between fields; moved out of any field; or even if the name of the field is changed.  To do this, I added a formula in the EventXFMod cell of the Events section of the Cow shape.

image

The formula is:

=CALLTHIS(“GetContainerProp”,,”Prop.InField”,”Prop.FieldName”, “Prop.Quadrant”)

This is a call to the GetContainerProp macro with the parameters Prop.InField , Prop.FieldName and Prop.Quadrant.

The VBA method, GetContainerProp , is based on the ListShapesThatContain example in the Shapes \ Spatial Neighbor List in the Visio SDK Code Samples Library.

Public Sub GetContainerProp(ByVal shp As Visio.Shape, _

    ByVal shpProp As String, ByVal containerProp As String, _

    ByVal quadrantProp As String)

Dim vsoShapeOnPage As Visio.Shape

Dim intTolerance As Integer

Dim vsoReturnedSelection As Visio.Selection

Dim strSpatialRelation As String

Dim intSpatialRelation As VisSpatialRelationCodes

    'Abort if source cell not found

    If shp.CellExistsU(shpProp, Visio.visExistsAnywhere) = 0 Then

        Exit Sub

    Else

        'Remove current formula

        shp.CellsU(shpProp).FormulaForceU = "="""""

        If Not shp.CellExistsU(quadrantProp, Visio.visExistsAnywhere) = 0 Then

            shp.CellsU(quadrantProp).FormulaForceU = "="""""

        End If

    End If

    'Initialize string

    strSpatialRelation = ""

    'Set tolerance argument

    intTolerance = 0

    'Set Spatial Relation argument

    intSpatialRelation = visSpatialContainedIn + visSpatialTouching + visSpatialOverlap

    'Get the set of spatially related shapes

    'that meet the criteria set by the arguments.

    Set vsoReturnedSelection = shp.SpatialNeighbors _

        (intSpatialRelation, intTolerance, 0)

    'Evaluate the results.

    If vsoReturnedSelection.Count = 0 Then

        'No shapes met the criteria set by

        'the arguments of the method.

    Else

        'Check each shape in the selection

        For Each vsoShapeOnPage In vsoReturnedSelection

            'Abort if target cell not found

            If Not vsoShapeOnPage.CellExistsU(containerProp, _

                    Visio.visExistsAnywhere) = 0 Then

                'Check that the source Pin is over the container

                If vsoShapeOnPage.HitTest(shp.Cells("PinX").ResultIU, _

                        shp.Cells("PinY").ResultIU, 0) Then

                    shp.CellsU(shpProp).FormulaForceU = _

                        "=GUARD(" & vsoShapeOnPage.NameID & "!" & containerProp & ")"

                    'Enter formula in the optional quadrant cell

                    If Not shp.CellExistsU(quadrantProp, Visio.visExistsAnywhere) = 0 Then

                        shp.CellsU(quadrantProp).FormulaForceU = _

                            "=GUARD(RECTSECT(" & vsoShapeOnPage.NameID & "!Width, " & _

                                vsoShapeOnPage.NameID & "!Height," & _

                                "PinX - (" & vsoShapeOnPage.NameID & "!PinX - " & _

                                    vsoShapeOnPage.NameID & "!LocPinX + " & _

                                    vsoShapeOnPage.NameID & "!Width * 0.5 ), " & _

                                "PinY - (" & vsoShapeOnPage.NameID & "!PinY - " & _

                                    vsoShapeOnPage.NameID & "!LocPinY + " & _

                                    vsoShapeOnPage.NameID & "!Height * 0.5) , 0 ))"

                    End If

                    Exit For

                End If

            End If

        Next vsoShapeOnPage

    End If

End Sub

This code works by utilizing the Shape.SpatialNeighbors property to get a list of the shapes that the cow is contained in; touching or overlapping (these are the Relation parameter). Each shape in this list is checked to see if has the required container cell, and, if it does, the Shape.HitTest method is used to check that the feet of the cow (its PinX and PinY) are in the field. To have a closer approximation of the cows feet position, and changed the LocPinY of the cow shape to be Height*0.

image

It is worth noting that the default for the SpatialNeighbors property is to include Visible geometry sections, and in the case of the imported Clip Art image that I used, it worked because a Geometry section existed with Geometry1.NoShow = FALSE, even though there is no fill or lines.

image

Note that the quadrant numbers for the RECTSECT() function start on the right, then increase anticlockwise:

image

There two cases that are not covered by my example, namely, when a field is deleted or moved away from any cows that are in it. The latter case can be handled by fixing the PinX and PinY of the Cow to be relative to the underlying field, but the former case needs code to react to the missing field. This can be done, but is beyond the scope of this post. In any case, I would recommend using events in an Add-In (usually vb.net or C#) for a more complete and robust solution, since lots of simultaneous individual calls with CALLTHIS() in VBA will degrade performance at some point.

Download the example Visio file from WhichFieldIsTheCowIn.vsd

Related

Filed Under: ShapeSheet Formulas, VBA, Visio Tagged With: Shape Data

About David Parker

David Parker has 25 years' experience of providing data visualization solutions to companies around the globe. He is a Microsoft MVP and Visio expert.

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

  • LinkedIn
  • Twitter

Recent Posts

  • Update to LayerManager add-in for non-English users
  • Pushing Data Visualizer in Visio beyond its limits
  • Pushing Data Visualizer in Visio to the limits!
  • Teams Tuesday Podcast Recording about Visio
  • Linking Data to Visio Shapes in Code

Categories

Tags

Accessibility Add-Ins Connectors Containers Data Export Data Graphics Data Import Data Visualizer Educational Excel GraphDatabase Hyperlinks Icon Sets JavaScript Layers Legend Link Data to Shapes Lists MSIgnite MVP Office365 Org Chart PowerApps PowerBI PowerQuery Processes Shape Data Shape Design ShapeSheet ShapeSheet Functions SharePoint 2013 SQL Teams Themes Validation VBA Video Visio Visio 2007 Visio 2013 Visio for the Web Visio Online Visio Services Visio Viewer Webinar

Footer

bVisual Profile

The UK-based independent Visio consultancy with a worldwide reach. We have over 25 years experience of providing data visualization solutions to companies around the globe.

Learn more about bVisual

  • LinkedIn
  • Twitter

Search this website

Recent posts

  • Update to LayerManager add-in for non-English users
  • Pushing Data Visualizer in Visio beyond its limits
  • Pushing Data Visualizer in Visio to the limits!
  • Teams Tuesday Podcast Recording about Visio
  • Linking Data to Visio Shapes in Code

Copyright © 2023 · Executive Pro on Genesis Framework · WordPress · Log in