How to hide shapes based on numerous cell values

I have a tricky one. I am creating an org chart in powerpoint and have about 400 different boxes that will need to get various color coded shapes in them. I am planning on pasting linked metafiles and would like the shapes to be dynamic and change as I make edits to the associated spreadsheet. I have a spreadsheet with about 400 lines and within each line are 10 different scenarios, each column and each line will get a 0's or -1's.

I am trying to write code that will automatically hide Oval 1 when Cell A1 = 0 and unhide i when A1 = -1.



A B C D E F G H I J 1 2 3 4 5 6 7 8 9 10
1 -1 0 0 0 -1 0 0 0 1 0 OVAL 1 Oval 2 ...
2 0 0 -1 -1 0 1 Oval 11 oval 12 ... ... ... ... ... ... ... ...

I am able ot do it pretty easily with the following code, but always have trouble with the references and having the loop work properly. (ie, had the idea to calculate the oval # by doing Row of the oval * 10 + the column (eg, Oval Row 1*10+column2 = Oval 12).

Basically want to do the following code on each of the 10 scenarios and hide/unhide the associated ovals in the adjacent columns.

Any ideas?

#

Please Login or Register  to view this content.

#

1. I created a table from column A to J, rows 1 to 10; placing "=0" in each cell (you need the equal sign since you are using the calculate event trap and need to have a formula).
2. I created a grid of ovals named across "Oval 1" to "Oval 10" (in row 1), "Oval 11" to "Oval 20" (row 2), etc to 10 rows.
3. I added the following code in the Worksheet_Calculate event trap.
4. Anytime I change the value in the table to "=-1" (actually any non-zero value), the corresponding oval is hidden. Change back to "=0", and it appears.

Please Login or Register  to view this content.

This is another method, a little more dynamic (and moved to Worksheet_Change event to remove the need for "=").
In this version, simply change the constants to meet the needs of your data grid (the oval names still need to match the number of data points):

Please Login or Register  to view this content.