A recent TechNet forum question asked if it was possible to change to behaviour of the Grid shape, found under ‘Charting Shapes’ stencil. The existing shape’s size is based on the total number of columns and rows whether they are visible or not. I thought I’d take a look at one way of changing this…
Current behaviour
The individual cell size of the grid shape is based on its overall width and height.
You can adjust number of visible rows and columns from one to ten via Shape Data and it’s here that the problem becomes apparent.
The trouble with keeping the parent size fixed and larger than its visible geometry is that it interferes with things like page auto sizing and also shape layout.
The reason that the shape displays this behaviour is that the individual cell shapes (children of the parent group), don’t take into account their respective visibility either in terms of their size or position. If you open the Drawing Explorer (Developer tab), select a cell child shape and click Show ShapeSheet from the context menu you’ll see the following:
Making the cells aware
One way around this problem is to make the cells aware of whether they should be visible or not.
You can start be adding a couple of indexing User Cells to the cell shape so that the cell shape knows in which row and column it’s meant sit. Then you add another User Cell to the shape that will refer back to the parent (Sheet.1 in this instance) to see if it should be shown or not:
Can you read that? It’s a bit of a squeeze, but the User.IsVisible cell just returns false if either, the row index is greater than the parent Rows count or, the column index is greater than the parent columns count. Otherwise it returns true – i.e. do display the cell shape.
Once you know the visibility you can move on to attaching the right behaviour in the positioning of the cell shape. The PinX / Y formulae are fairly lengthy so I’ll split them over a couple of lines for clarity:
The above formulae basically position the cell shape at grid reference A1 if the shape should not appear, or an indexed position if it should. This is combined with the a reference from the cell shape’s Geometry1.NoShow cell to the User.IsVisible cell to finally control both position and visibility.
Resizing modes
So that deals with the non-visible cells problem, but I thought I’d add another sizing method to suit different scenarios:
- Size parent to cells
- Size cells to parent (the current Grid shape’s default behaviour)
For the first option I’ve added two Shape Data rows to enable the user to set the default row and column height. Using this sizing mode, the grid shape’s overall width and height is determined by a multiple of the visible rows and columns, rather than the second option that divides the available space by the number of visible rows and columns.
Adding an IF function to the Width and Height cells allow the values to be based on the appropriate source: a multiple or a straight direct size. The latter of these two options is achieved by using the SETATREFEXPR function, which acts as a holder for the incoming value rather than replacing the entire cell formula.
A last change to ShapeSheet adds a couple of context menu items to enable toggling between the two resize modes.
Creating 100 children
All of the cell shape changes need to be repeated, or at least duplicated across all of the remaining 99 child shapes. This would be pretty laborious to do manually and is a great example of where code can be very helpful. I’ll go into the code required in the next post, but if you just want to download the shape and use it as is then you can do that here:
