Data validation - Whole Numbers

**khalid79m** · 10-02-2007, 11:54 AM

how can I set a cell so that you can only type in 1 , 2 , 3, 4, 5

I know I can use the validation but people can just copy a cell and paste it over.

**VBA Noob** · 10-02-2007, 11:56 AM

Under allow > select whole numbers

Min 1
Max 5

VBA Noob

**BigBas** · 10-02-2007, 11:56 AM

Either you can set up a validation (as you mentioned) AND protect your worksheet to prevent people from copying/pasting over the validation, or you can use VBA (if you don't mind that) to prevent people from entering an incorrect value.

**VBA Noob** · 10-02-2007, 11:59 AM

Sorry

Misread the question.

See if link helps

http://www.j-walk.com/ss/excel/tips/tip98.htm

VBA Noob

**VBA Noob** · 10-02-2007, 12:08 PM

Or a event macro. Right click sheet > select view code and paste in the below. Change range as required

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
If Target.Value > 5 Then
Application.Undo
MsgBox "Please enter number between 1 & 5"
End If
End If
End Sub

VBA Noob

**khalid79m** · 10-02-2007, 12:12 PM

Sorry where can I specify allow ?

**VBA Noob** · 10-02-2007, 12:14 PM

See link point 3.

http://www.contextures.com/xlDataVal01.html#Apply

VBA Noob

**khalid79m** · 10-02-2007, 12:35 PM

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
If Target.Value > 5 Then
Application.Undo
MsgBox "Please enter number between 1 & 5"
End If
End If
End Sub

HOW CAN I ADPAT THIS SO THAT Z3:Z500 IS RESTRICTED TO 1 , 2 OR 3 AS WELL AS THE ABOVE.

**VBA Noob** · 10-02-2007, 12:40 PM

Change to

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Z3:Z500")) Is Nothing Then
If Target.Value > 3 Then
Application.Undo
MsgBox "Please enter number between 1 & 3"
End If
End If
End Sub

VBA Noob

**BigBas** · 10-02-2007, 12:48 PM

This is just a minor change to VBA Noob's code to disallow decimal numbers from being used in place of whole numbers:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Z3:Z500")) Is Nothing Then
If Target.Value > 3 Or Target.Value <> Int(Target.Value) Then
Application.Undo
MsgBox "Please enter number between 1 & 3"
End If
End If
End Sub

**VBA Noob** · 10-02-2007, 12:54 PM

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
If Target.Value > 5 Then
Application.Undo
MsgBox "Please enter number between 1 & 5"
End If
End If
If Not Intersect(Target, Range("Z3:Z500")) Is Nothing Then
If Target.Value > 3 Then
Application.Undo
MsgBox "Please enter number between 1 & 3"
End If
End If
End Sub

VBA Noob