VBA code to allow only numeric values in a cell

[robertvdb]

in a certain cell range, only integer numbers are allowed for data entry.

So the user may enter 123, but not 123.0 or 123.00 and so on.

Also, any use of letters (a-z) or symbols ( for example & ' ( § % and so on) is obsolete.

Can anyone help me to code this ? Thanks


EDIT: PS correction: I should have written: to allow only numeric CHARACTERS 0-9 in a cell

 

[Legacy 143009]

Like this?

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 And Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    If InStr(Target.Text, ",") + InStr(Target.Text, ".") > 0 Or Not IsNumeric(Target.Value) Then
        Target.Clear
        Target.Select
    End If
    Application.EnableEvents = True
  End If
End Sub

This example will work for column A.

 

[robertvdb]

Thanks Flashbond. Seems to work.

Two questions:
1) what is the sense of adding Application.EnableEvents = False and at the end Application.EnableEvents = True

2) suppose in cell A3, the user wants to replace a valid existing value, say 100. Then when he enters some invalid number, say 101.00, I want the 100 to reappear.

 

[Legacy 143009]

1) what is the sense of adding Application.EnableEvents = False and at the end Application.EnableEvents = True

We are clearing a cell. This will trigger another worksheet_change event before the preivous one completes. So, it's best to mute all event listeners in such situations.

2) suppose in cell A3, the user wants to replace a valid existing value, say 100. Then when he enters some invalid number, say 101.00, I want the 100 to reappear.

I don't think such thing is possible. I tried the same. If a cell is formatted as "0", it will display 100 even if it was entered as 100.00. Even after if you'll have a dubt then this will be a final solution:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 And Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    If Not IsNumeric(Target.Value) Then
      Target.Clear
      Target.Select
     ElseIf InStr(Target.Text, ",") + InStr(Target.Text, ".") > 0 Then
      Target.Value = Int(Target.Value)
      Target.NumberFormat = "0"
    End If
    Application.EnableEvents = True
  End If
End Sub

 

[robertvdb]

Thanks Fluff !

 

[robertvdb]

Sorry ! Thanks Flashbond !

 

[Legacy 143009]

Thanks Fluff !

It is an honor for me to be mixed up with Fluff

Glad it did work