Limiting Decimal Entry

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

Does anyone know if there is a way for me to limit the number of decimals
entered into a cell to 2? I don't want to truncate or round the entries.
It doesn't matter if an error message is raised, I just want to set a hard
limit.

Thanks
 
For cell A2, maybe this:

Select A2
From the Excel Main Menu:
<data><validation>
Allow: Custom
Formula: =INT(A2*100)=A2*100
Click [OK]

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
Hi Ron,

Thanks for the post. Unfortunately, this doesn't do what I'm looking for.
It's rounding the value as opposed to preventing the entry. I'm trying to
make the sheet as idiot-proof as possible. What I'd really like is
something like this: the user attempts to enter 1.234 but when they press
'4', either the keystroke is ignored or the validation pops up an error
message.

Thanks again,

Tony

Ron Coderre said:
For cell A2, maybe this:

Select A2
From the Excel Main Menu:
<data><validation>
Allow: Custom
Formula: =INT(A2*100)=A2*100
Click [OK]

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


Tony said:
Does anyone know if there is a way for me to limit the number of decimals
entered into a cell to 2? I don't want to truncate or round the entries.
It doesn't matter if an error message is raised, I just want to set a
hard limit.

Thanks
Click to expand...
Click to expand...
 
Ok...I understand what you hoping to do, but it may not be practical (or
even possible). Using the Data Validation, yes it lets the users type too
many decimal places, BUT it warns them of the error and prevents them from
committing invalid amounts..AND all without any VBA programming. Trapping
user keystrokes would involve programming...and...I seem to recall that no
macros run when Excel is in Edit Mode.

I hope that helps.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Tony said:
Hi Ron,

Thanks for the post. Unfortunately, this doesn't do what I'm looking for.
It's rounding the value as opposed to preventing the entry. I'm trying to
make the sheet as idiot-proof as possible. What I'd really like is
something like this: the user attempts to enter 1.234 but when they press
'4', either the keystroke is ignored or the validation pops up an error
message.

Thanks again,

Tony

Ron Coderre said:
For cell A2, maybe this:

Select A2
From the Excel Main Menu:
<data><validation>
Allow: Custom
Formula: =INT(A2*100)=A2*100
Click [OK]

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


Tony said:
Does anyone know if there is a way for me to limit the number of
decimals entered into a cell to 2? I don't want to truncate or round
the entries. It doesn't matter if an error message is raised, I just
want to set a hard limit.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
OK Ron, I'm dumb. I think what probably happened is that I put your formula
into place and didn't update the cell reference. Sorry 'bout that...

Looks like it's working as expected. I appreciate the help.

Thanks,

Tony


Ron Coderre said:
Ok...I understand what you hoping to do, but it may not be practical (or
even possible). Using the Data Validation, yes it lets the users type too
many decimal places, BUT it warns them of the error and prevents them from
committing invalid amounts..AND all without any VBA programming. Trapping
user keystrokes would involve programming...and...I seem to recall that no
macros run when Excel is in Edit Mode.

I hope that helps.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Tony said:
Hi Ron,

Thanks for the post. Unfortunately, this doesn't do what I'm looking
for. It's rounding the value as opposed to preventing the entry. I'm
trying to make the sheet as idiot-proof as possible. What I'd really
like is something like this: the user attempts to enter 1.234 but when
they press '4', either the keystroke is ignored or the validation pops up
an error message.

Thanks again,

Tony

Ron Coderre said:
For cell A2, maybe this:

Select A2
From the Excel Main Menu:
<data><validation>
Allow: Custom
Formula: =INT(A2*100)=A2*100
Click [OK]

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


Does anyone know if there is a way for me to limit the number of
decimals entered into a cell to 2? I don't want to truncate or round
the entries. It doesn't matter if an error message is raised, I just
want to set a hard limit.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Thanks for letting me know.....much appreciated.

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Tony said:
OK Ron, I'm dumb. I think what probably happened is that I put your
formula into place and didn't update the cell reference. Sorry 'bout
that...

Looks like it's working as expected. I appreciate the help.

Thanks,

Tony


Ron Coderre said:
Ok...I understand what you hoping to do, but it may not be practical (or
even possible). Using the Data Validation, yes it lets the users type
too many decimal places, BUT it warns them of the error and prevents them
from committing invalid amounts..AND all without any VBA programming.
Trapping user keystrokes would involve programming...and...I seem to
recall that no macros run when Excel is in Edit Mode.

I hope that helps.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Tony said:
Hi Ron,

Thanks for the post. Unfortunately, this doesn't do what I'm looking
for. It's rounding the value as opposed to preventing the entry. I'm
trying to make the sheet as idiot-proof as possible. What I'd really
like is something like this: the user attempts to enter 1.234 but when
they press '4', either the keystroke is ignored or the validation pops
up an error message.

Thanks again,

Tony

For cell A2, maybe this:

Select A2
From the Excel Main Menu:
<data><validation>
Allow: Custom
Formula: =INT(A2*100)=A2*100
Click [OK]

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


Does anyone know if there is a way for me to limit the number of
decimals entered into a cell to 2? I don't want to truncate or round
the entries. It doesn't matter if an error message is raised, I just
want to set a hard limit.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top