two space rounding

[Guest]

I have a spreadsheet that multiple users use. It allows them to capture
totalt time worked in a range of cells. They can enter anything from .25 to
24.00. The cells are formatted to round to two decimal places. I want to
restrict what can be entered into the cell to a value equal to or greater
than .25 and less than or equal to 24.00. I have accomplished this with the
validation feature.

Here is my problem. If they type in a decimal entry, I want them to be
restricted to .25, .50 or .75 (can be used with whole numbers, too, ie. 4.25
or 7.75).

How can I accomplish this easily?

Sometimes people will type in for example 8.24. The cell rounds up to 8.25
so it looks correct but the ultimate tally is off by .01

 

[Gareth]

One non-vba solution would be to use a data validation list of
"0.25,0.5,0.75,1 etc.".

This will also allow you to enter a custom error message that will
appear should the user enter 2.24 rather than 2.25, say.

If you need a VBA solution then post back. Personally, I would only use
VBA however if you are using VBA anyway - IMHO there's no point
introducing the (minor) headache of ensuring macros are enabled etc. for
multiple users.

HTH,
Gareth

 

[Guest]

that would be a long string wouldn't it? 24 * 4 = 76 enteries in the
valadation per cell. Wouldn't that weigh the program down a lot?

 

[Gareth]

I agree: it would be fairly long, 439 char by my reckoning (see below
code to make the string should you want it). But I shouldn't *imagine*
(I stress that because I haven't tested it) it would be that bad - Excel
wouldn't repeat it for every cell (it's smarter than that).

Oh... bunnies. I just tried pasting the string into the data validation
list and it's too long - I think 255 char is the limit.

Alternatives:

(1) Write the data validation source list onto the worksheet you're
using (in a hidden column). That *does* work for all 96 rows - I just
tried it....

(2) Revert to VBA and trap Worksheet_Change events to correct / abort
user data entry.

(3) Allow the incorrect entries and correct them in your formulae when
you total up.

HTH,

Gareth

----------------------------------
Sub temp()
Dim i As Single
Dim myString As String
Do While i < 24
myString = myString & i & ","
i = i + 0.25
Loop
myString = Left$(myString, Len(myString) - 1)
ActiveCell = myString
End Sub