Author Topic: Limit Cell Value to Increments  (Read 230 times)

0 Members and 1 Guest are viewing this topic.

rezingg

  • Jr. Member
  • **
  • Posts: 13
Limit Cell Value to Increments
« on: July 15, 2020, 04:32:31 PM »
So I can use BOUND() to limit the rotation angle of a cell to some increment, e.g. 90 deg. This works because the angle value is circular (i.e. is wrapping, the same values keep repeating as you go around the circle multiple times).
Is there a way to limit a linear cell value, such as "Width" to an increment?
What I would like to do is to limit the width of a shape to increments of 1/8 in. So when the user drags the handle of the shape to change width, it snaps to 1/8 in steps, similar to what BOUND() can do for the angle.
I tried to use BOUND(), but get a "circular reference" error, as I can't put fixed values as parameters, but need to refer back to the current value of the cell.
I know I can do this with a macro, but I would like to stay within the ShapeSheet, if possible.

vojo

  • Hero Member
  • *****
  • Posts: 1476
Re: Limit Cell Value to Increments
« Reply #1 on: July 15, 2020, 06:01:17 PM »
I think you will have to use one of the setatref functions for this.  However, these are very complicated and subtle functions
You should play with to understand

a visio guy example of constraining a control point in a circle is an example of how to do this.

http://visguy.com/vgforum/index.php?topic=6383.0
http://www.visguy.com/2018/11/07/constrain-control-handle-to-circle/










rezingg

  • Jr. Member
  • **
  • Posts: 13
Re: Limit Cell Value to Increments
« Reply #2 on: July 15, 2020, 07:15:07 PM »
Excellent!
"Height=INT(SETATREFEXPR(0.5 in)*(8 in)+0.5)/(8 in)"
is doing exactly what I needed, snap to 1/8".

Thanks vojo!

vojo

  • Hero Member
  • *****
  • Posts: 1476
Re: Limit Cell Value to Increments
« Reply #3 on: July 16, 2020, 07:16:00 PM »
I guess this is a representation of what you did...this formula has some errors around the brackets and height).
but glad you got what you need