Referencing a Cell from the custom property window

[TheFlyingPig]

I have a shape that I want to change its border colour based on a property, but I want the property to be able to be specified by non technical users without them touching the shapesheet.

I have actually found a way to do it and I thought i'd post it here because this Visio guy has heped me to do things in a number of things that I have been struggling with and this was really a euricka moment!

The method I have used involves using a setf in a seperate cell so imagine three cells, a custom property cell where the user will enter the formula in string format (prop.CellRef), a user cell that has the setf formula in it (user.SetF), and a second user cell that holds the final cell reference and therefore the value of that referred cell (user.CustPropFormula).

If you put the below formula in user.SetF:
    =SETF("user.CustPropFormula",INDEX(0,Prop.CellRef))

This will take the string value contained within the string contained in the cell Prop.CellRef using the INDEX function, it will then set the user.CustPropFormula cell with the content of that string which in this case is a formula cell reference, it is not a string from a list which all the other methods I tried gave me.

Apologies if you had all figured this method out already, if there is in fact a better way to do it then please do let me know, setting up the 3 cell circle is slightly cell heavy to be honest but I couldn't find any formula that took strings and expended their content into the cell other than setf.

Kind regards,

Pig

[Visio Guy]

Thanks for the tip, TFP,

When using the SETF ShapeSheet function, it is a good idea to pair it with GETF. So your function would look like this:

User.SetF = SETF( GETREF( user.CustPropFormula ) ,INDEX(0,Prop.CellRef))

What GETF does is eliminates the need for quotes around the reference to a cell. So now, if the User.CustPropFormula's name gets changed to User.CPF or User.Setter, the formula will still be good.

When SETF was first introduced, GETF didn't exist, so quotes were necessary. But cell-name changes can break these setters. With GETF, you don't have to worry about it anymore.

[TheFlyingPig]

Good point!  Hadn't thought of that,

Thanks a lot Visio Guy!