CODEWITHSUNDEEP
excelvba
Uder Moreira
Uder Moreira

Reputation: 932

Excel: input data validation to allow only numbers

Is there any way to not allow users enter alpha characters in a cell that I need only numbers?

I would greatly appreciate any help.

Upvotes: 0

Views: 17567

Answers (2)

Phillip Wong
Phillip Wong

Reputation: 11

Assuming input cell is A1

In the Data Validation menu

Allow: Custom

Formula: =isnumber(A1)

Upvotes: 1

user2204167
user2204167

Reputation:

As already mentioned it can be achieved by Data Validation... but there is a catch.

First method : Data validation

  1. ALT+D+L
  2. Allow : Whole Number
  3. Data : greater than (select what you want)
  4. Minimum : 0

CATCH : But Data Validation doesn't check the value if it is copied from some other cell. That is, if I copy alpha value from a different cell & paste / paste special (value) it over the cell, it will accept it. It will not restrict me.

Hence Second Method : VBA

Private Sub Worksheet_Change(ByVal Target As Range)
    Const CELL_ADDRESS = "$A$1" 'change cell
    If Target.Address = CELL_ADDRESS Then
       If Not IsNumeric(Target.Value) Then
          MsgBox "Wrong value", vbCritical, "666bytes"
          Target.Value = vbNullString
       End If
    End If
End Sub

Edit: For a range :

Private Sub Worksheet_Change(ByVal Target As Range)
    Const CELL_ADDRESS = "$D$2:$E$4000" 'change range
    If Not Application.Intersect(Target, Range(CELL_ADDRESS)) Is Nothing Then
       If Not IsNumeric(Target.Value) Then
          MsgBox "Wrong value", vbCritical, "666bytes"
          Target.Value = vbNullString
       End If
    End If
End Sub

Insert this VBA code in the sheet module where you want to implement this and change CELL_ADDRESS. If you want you can do a lot of stuff using the Worksheet_Change event, so please play as you like with this code. Hope this helps! :)

Upvotes: 1

Related Questions