using spin buttons

[Rob]

Hi to all!

I want to change the value in a cell from 1 to 10 in steps of 0.5 by
using a spin button. After placing the spin button, I Ctrl-click the
button and I point to Format Control. On the Control Tab one can set a
minimum and maximum value plus the incremental steps.

My guess was: set Incremental change to a value of 0.5 and you all set.

Decimals are ignored, only integers seem to work.

Did a quick check in Excel 2003 in Windows - same thing happening.

Any simple solution? Guess I have to add some VBA code.

TIA - Rob

 

[JE McGimpsey]

I want to change the value in a cell from 1 to 10 in steps of 0.5 by
using a spin button. After placing the spin button, I Ctrl-click the
button and I point to Format Control. On the Control Tab one can set a
minimum and maximum value plus the incremental steps.

My guess was: set Incremental change to a value of 0.5 and you all set.

Decimals are ignored, only integers seem to work.

Did a quick check in Excel 2003 in Windows - same thing happening.

Any simple solution? Guess I have to add some VBA code.

Yes, the SmallChange property of a spinbutton control requires a long
integer, so fractional changes aren't allowed.

One could use VBA, but it would be simpler to use a "helper cell".

For instance, say you want your spinner to increment cell A1. Link the
spinner to an out of the way cell, say Z1, with minimum = 2, maximum =
20 and increment by 1. Then in A1 enter

=Z1/2

For aesthetics, you could hide column Z. Or you could put the linked
cell on another sheet, and hide that sheet.

 

[Rob]

Yes, the SmallChange property of a spinbutton control requires a long
integer, so fractional changes aren't allowed.

One could use VBA, but it would be simpler to use a "helper cell".

For instance, say you want your spinner to increment cell A1. Link the
spinner to an out of the way cell, say Z1, with minimum = 2, maximum =
20 and increment by 1. Then in A1 enter

=Z1/2

For aesthetics, you could hide column Z. Or you could put the linked
cell on another sheet, and hide that sheet.

Thanks for your quick response, got it working in my worksheet.
Thanks again.

Robert