Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Set rng2check = Range("A1:A10") 'you can adjust this how you wish
       If Not Intersect(ActiveCell, rng2check) Is Nothing Then ActiveCell.NumberFormat = "@"
      Set rng2check = Nothing
    End Sub

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.

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error Resume Next 'on an error leave events enabled
 Application.EnableEvents = False
   inputCheck = Target.Text
   inputCheck = Replace(inputCheck, ":", ".")
   inputCheck = CDbl(inputCheck)
    'MsgBox (inputCheck)
   Range(Target.Address).NumberFormat = "General"
  Range(Target.Address) = inputCheck
 Application.EnableEvents = True
End Sub

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.