I need to format some Excel cells to allow currency (decimal point) only to be entered. Some employees completing the form enter a colon (:) instead of a decimal point which then results in an incorrect total. Is it possible to format or restrict a cell so that an error message appears if any symbol other than a decimal point (.) is entered? Thank you for your help.
Although this is not the most elegant way to do this, and I recommend you test it thoroughly. This seems to work. Adjust this as necessary for the problematic input column. This is set to a range now, not the whole column, so that formulas will work in the column.
First set the cell format so that it is not automatically converted to something for you. Set the cell format to be what is actually typed using the Selection Change event.
Then check the input for colons and replace them, covert the value to a Double or Currency and reset the final cell format to General, or Currency, whatever works for you.
Someone may point out a better way to do this. Changing the cell format to text first may not be the best solution, but it achieves what you want. Of course other data validation might be a good idea
You could extend this to replace other characters than colons as well I am sure.
You could use data validation, where you specify that the only value(s) acceptable for your probelm cell can come from a pre-specified list range. You can hide this range anywhere you like. It can contain any values you like. I your case, it will probably be a single cell containing a period.
The Validation dialog will allow you to specify an error message whenever a value that is not from the list is input. It will also give you a nice dropdown menu so that the users may select the appropriate value.