Brainteaser - I really need your expertise

[Maureen5433]

First, let me preface this question by saying, absolutely, that I cannot use macros to solve this problem. Having said that...

I am creating an interactive survey and need the user to be able to input currency to 2 decimal places ($1.00 for ex) with the "cents" increasing by quarters. I woud like to use a spinner button but it only allows for increases in whole # increments, as far as I can see. Does anyone know a work around and or have an idea for this? I am thinking I would set it up as follows:

cell A1 would be a spinner increasing by 1 each for the whole dollar amount
cell B1 would be the "cents" part of the # and would need to move, with a spinner, in increments of .25

I know this is confusing, I hope it is decipherable. Any help is appreciated. Thank you.

[Mark W.]

Why not just have 'em type the value and rely on data validation to insure that value is a multiple of .25...

=MOD(A1,0.25)=0

Most data entry folks can type faster than they can spin!

[Maureen5433]

I had thought of that but the idea is for them to use a spinner to select their #'s, this is in keeping with the look of the survey and also because the survey will be published internationally and less typing, the better.

Thanks.

[lenze]

Link your spinner to a hidden cell on another sheet . Name the cell something like 'Amount'. In the cell where you want your dollar value to show, type in the formula = 0.25*Amount. Format as currency.

BTW, A macro approach is much more efficient. Using a spinner from the control toolbar the code would be

Private Sub SpinButton1_Change()
Sheet1.Range("$A$3").Value = Sheet1.SpinButton1.Value * 0.25
End Sub

[Maureen5433]

Thank you for the idea, I will give it a try. I realize that a macro would be the best way to accomplish this, however, we have found that there can be problems when we send out documents with macros as some people set their macro security to high and aren't even aware that there are macros involved.

Thanks again.

MSG