Creating a Visio shape with an expanding row count

In this post I'm going to look at one option for creating a table style shape that increases its row count as it is resized vertically.

I had a question the other day about how you might build a table or grid style shape that increases the number of rows it contains as it's expanded. The question included a helpful mock up of the desired behaviour:

Looking at this, some initial questions I'd start off with are:

• Is the maximum number of rows fixed or is there some reasonable upper limit that would cover all scenarios? (Could the sub-shapes be pre-built?)
• Do the rows have to be individually selectable - might each row have an individual set of Shape Data applied to it? (If yes, suggests separate sub-shapes)
• Is the formatting (font, style, background) going to be uniform across each row? (If not, then again suggests individual sub-shapes)
• Are there likely to be many instances of this shape on a page? (If there are many hundreds or thousands then you may pay more attention to not pre-loading with potentially unused sub-shapes)

If the number of instances is a concern then you could possibly opt for generating new geometry sections rather than sub-shapes or, potentially use a Structured Diagram (SD) List type shape where adding and removing row items would be more straight forward.

It's also worth bearing in mind that a group shape can also be used as a drop target. This, in combination with another shape that acts as a drop source shape, allows you to build logically grouped shape sets. However, if you want some additional logic to handle positioning of the sub-shape, this can become tricky without code.

So the two main options that spring to mind are a single group shape + hidden sub-shape rows and a Structured Diagram - List type shape.

Given what appears to be a limited number of rows I'm going to opt for building the former option.

Revealing hidden rows

One of the issues that this type of shape throws up is that of how to aggregate the heights of sub-shapes, some of which you may intend to have a height of 0. You could have a formula in the group shape that directly references and sums the height of each shape, but I'm uncomfortable with the parent having too much knowledge of its children. Given that Visio's collection of ShapeSheet functions lack any kind of iteration functionality, this is probably a situation where I might break that rule.

In this case you're going to want to know where the bottom of each shape might be if it were visible. One way around this is to ensure that the rows are not of a variable height and that's the approach I'm going to take in this instance.

So let's get going and create a shape that contains 10 pre-defined rows

1. I'm going to start by drawing a basic rectangle (Ctrl+8) in a new blank drawing.
2. In the group drop-down on the ribbon select 'Convert to group'
3. I'll open up the ShapeSheet window and create Shape Data, User, Text Transform and Connection Points sections by right-clicking and selecting 'Insert section...'

My plan is to have the group shape host the heading and the sub-shape, when I add them, become the row items. Before I start the construction I'll add some variables that the other components of the shape can reference.

4. In the new User section I'll add the following:
   

I've just tucked that longer formula under its row, but you should be able to see the following:

• the heading height is going to be governed by text height for the group shape
• the row item height is set to an arbitrary height of 4mm (I'll come back to this later)
• the minimum height for the whole group shape will be the sum of the heading and one row item
• the maximum height for the whole shape will the heading height + 10 times the row item height

Now the basic variables are in place I'll setup the heading.

5. First change the positioning of the shape's text by altering the following cells in the Text Transform section

• TxtLocPinY = TxtHeight
• TxtPinY = Height
• TxtHeight = User.HeadingHeight

6. With the text area in the right place I'll adjust the geometry section to the same area so the background mirrors the heading area. These days, Visio gives you a 'relative' geometry section by default:
   
   Although there are some benefits to this what you need here are absolute rows instead, so right click on the row numbers and change them to their absolute alternatives:
   

You should now have a shape that looks something like this (I've add the text so you can see where it sits):

There are some options here about whether you want the border to follow the perimeter of the entire group shape rather than the heading, but I'll leave that to you to adjust if that's the way you want to go. If that is the case then I would add a second geometry section and use the first for the fill and the second for the border, setting NoFill and NoLine as appropriate.

In any case, I'm now ready to start to add the row shapes. The basic strategy here is to add the first row shape and set an index cell that will drive the position of the remaining rows.

However things are complicated a little bit by the fact that there's more than one column in the table. More choices - you could go for a single shape and then use tabs to position the two pieces of text or go for two separate shapes. The tab option doesn't handle text wrapping very nicely and so I'm tempted towards two separate shapes. This has another advantage that if some kind of conditional formatting of the value, as opposed to the label, becomes necessary you'd be able to accommodate this fairly easily.

Given that you're now looking at two shapes in a single row, you need some method to set the column widths. To do this I'm going to add a control handle just under the heading.

7. Right-click in the group shape ShapeSheet and add the 'Controls' section.

8. Rename the first row in the new section to 'Column1' and set the cell formulas as follows:

• Controls.Column1.X = BOUND(Width*0.6667,0,0,0,Width)
• Controls.Column1.Y = Height-User.HeadingHeight
• Controls.Column1.YCon (Y Behavior) = 1 (visCtlLocked)

Using these formulas, you're constraining the X movement of the control to horizontal extents of the shape with the BOUND function and setting and locking the Y position to the base of the heading.

7. Before you add the new row shapes, you just need to set the LockCalcWH cell (lock calculate width and height) so that the group doesn't resize when new shapes are added.

So, on to the first sub-shape - I'll just deal with the label shape as this first shape is going to act as the main template for all label and value shapes and it will hold all of the logic. The text construction and position will be driven by its row and column indices, which I'll add in a second. (Note that in the following formulas, the group shape ID is '1'.)

8. Add a new rectangle somewhere in the middle of the group shape, select both the group shape and your new rectangle and click Group / Add to Group.

9. Open the new sub-shape's ShapeSheet and add new Shape Data and User sections.

10. In the Shape Data section you need two rows, one named Prop.RowIdx and the other Prop.ColIdx

11. In the User section you need two rows as follows:

• User.IsHidden = Sheet.1!Height<Sheet.1!User.HeadingHeight+(Sheet.1!User.RowItemHeight*(Prop.RowIdx+1))
• User.Text = IF(Prop.ColIdx=0,"SIGNAL"&Prop.RowIdx+1,FORMAT(Prop.RowIdx,"{00}"))

12. Now the logic is in you can set the size and position cells:

• Width = GUARD(IF(Prop.ColIdx=0,Sheet.1!Controls.Column1,Sheet.1!Width-Sheet.1!Controls.Column1))
• Height = GUARD(Sheet.1!User.RowItemHeight)
• PinX = GUARD(IF(Prop.ColIdx=0,0,Sheet.1!Controls.Column1))
• PinY = GUARD(IF(User.IsHidden,Sheet.1!Height,Sheet.1!Height-(Sheet.1!User.HeadingHeight+(Sheet.1!User.RowItemHeight*Prop.RowIdx))))
• LocPinX = 0 mm
• LocPinY = Height

13. Although you PinY formula shifts the shape behind the heading if it should be hidden, you also need to hide the text, so, in the Miscellaneous section set:

• HideText = User.IsHidden

14. The final step is to insert a field by editing the text (F2) and clicking Insert / Field (Ctrl + F9). Once in the Field dialog, select 'Text' from the User-defined Cells category.

You should now have a shape that looks something like this:

Test & Lock down

It's at this stage that you should check that sub-shape functions correctly and you should play around with the Shape Data row and column indices in the sub-shape. If there are any further changes that you want to make, then this is the time to do it:

Once your happy, you can move on to locking down the the protection section.

15. In the group shape, add a new User cell named IsProdMode and set it to True.

16. Back in the sub-shape (in the Protection section) set both LockDelete and LockTextEdit to: Sheet.1!User.IsProdMode

17. Also in the sub-shape set the other cells to 1:

• LockWidth
• LockHeight
• LockAspect
• LockMoveX
• LockMoveY
• LockRotate
• LockVtxEdit

It's also difficult to know at this stage how formatting might be applied to the shape. In general, if a fill colour is applied the a group shape via the UI then all of the sub-shapes will receive that fill as well. You can prevent this from happening by setting the LockFromGroupFormat cell in each sub-shape, however this then removes a simple way of set sub-shape formatting when you do want to have this effect.

So what I'm going to do is add a right-click menu option to allow the user to toggle this cell in the sub-shape. This will allow the user to turn off the lock, apply some formatting to the whole shape, turn it back on and just change the header. It's not a perfect solution but allows for some level of flexibility.

18. Open the group shape's ShapeSheet

19. Add a User cell:

• User.LockSubFormatting = TRUE

20. Right-click and add an Actions section, renaming the first row to Actions.LockSubs

21. Set the following cells:

• Actions.LockSubs.Menu = "Lock sub-shapes from formatting"
• Actions.LockSubs.Action =
  SETF(GetRef(User.LockSubFormatting),NOT(User.LockSubFormatting))
• Actions.LockSubs.Checked = User.LockSubFormatting

22. Open the sub-shape's ShapeSheet.

23. Set the following cell:

• LockFromGroupFormat = Sheet.1!User.LockSubFormatting

Duplicate

Next up is the duplication phase.

24. Reset the sub-shape row and column index Shape Data rows to 0.

25. Right-click on the group shape and select Group / Open Group [This opens the group editing window (note the part of the window caption) and this allows you to work at the sub-shape level.]

26. Select the row item sub-shape hit Ctrl+D 19 times to duplicate the shape, and close the group editing window. (note you won't see any changes as all of the shapes will be placed in the same position)

27. Back in the main window click on Developer / Drawing Explorer and expand the Foreground tree node all the way down to the Shapes node of the group shape

28. Now, for each sub-shape select the item in the tree and then edit the row and column values to arrange all of the shape.
    [Note that once there are more then a few shapes you may want to write some code to iterate through the shapes to do the same job - if that's of interest then have a look at the code at the bottom of this post, which does something similar]

29. With all of the sub-shapes added you can also lock down the group by setting LockGroup to 1 in the group shape.

The shape should now look something like this:

Constraining the height

So all of the internal logic is now done so I just want to finish the shape build by constraining how the actual height of the group shape behaves.

30. In the group shape's ShapeSheet I'll add three more cells:

• User.RawHeight = 70mm
• User.VisibleRows = INT((User.RawHeight-User.HeadingHeight)/User.RowItemHeight)
• User.CalcHeight = MIN(User.MaxHeight,MAX(User.MinHeight,User.HeadingHeight+(User.RowItemHeight*User.VisibleRows)))

31. With those setup, you can set the Height cell:

• Height = SETATREF(User.RawHeight,SETATREFEXPR(70.4435 mm),1)+User.CalcHeight

So what's happening here?

If you've not come across the SETATREF function before, you can think of it as a method to redirect cell traffic. When the user drags the shape's height handles, Visio fires a new value into the Height cell. The SETATREF function handles this (via the SETATREFEXPR function) and forwards the value onto a cell of your choosing - in this case User.RawHeight. This in turn triggers User.VisibleRows and finally User.CalcHeight gets its turn to calculate the desired height based on a multiple of User.RowItemHeight. If you look at the third argument of the SETATREF function, you'll spot that this is '1', which tells the function to ignore the resulting value and just evaluate to 0. At the end of all of that the '+User.CalcHeight' is the resulting value that the shape will actually be set to.

If you're interested in reading more about SETATREF then I have another post here (FormulaU and FormulaForceU in Visio)

Wrap up

I won't step through the setting of the heading font as this is just via the UI, but once that's done the final step is to create a master shape. You can do this by dragging the shape onto the document stencil, but if you want to get more details about this process then you can checkout the 'Creating Masters and Stencils' section in 'Developing Microsoft Visio Solutions'

You can download a completed version of the above here:

• MultiRowShape.vsdx (20.4K) [Remove ".zip" extension on download]