Insist on 3 decimal places given

[L33]

Hi,

I am assisting with the creation of something where the user has to be made to enter a number with 3 decimal places, even if they're all zeros. We've got the custom format set up so as to display as 0.000, but obviously this just makes the user able to enter "12", it display as "12.000", at which point the user shrugs and moves on. We want to insist that they enter those three decimal places.

Is there any way of using data validation to create an input mask to do this? Or perhaps a simple piece of VBA that runs when that cell is changed - any pointers?

Or do we have to get into user form territory if we really want to do this? My colleagues not keen on that as this is literally the only field that we've got problems with, and it would mean re-designing the whole complex model he's done already.

thanks
L33

 

[Ruddles]

I'm sure there must be a better way but if no-one else can think of one, this is the best idea I could come up with...

Set the cell to Text when it's empty and use the Worksheet_Change event to detect when someone types into it. Check what they've entered via the event handler and if it's okay, reformat the cell to Number with three decimal places, otherwise display a message box (if that's what you want to do) and clear the cell.

This requires macros to be enabled.