Mar 26 2021 12:49 PM
Hope someone can help me on this.
I have 3 cells(A1,A2,A3) for which I want only Whole Numbers like 1,2,3 and do not want decimals like 0.1 or 0.5 and neither want alpha chars so I have set Data Validation for these cells to Whole Number. This seems to work fine when I try to enter any non whole number values manually it throws an error and accepts only whole numbers so till here this is good. But when I copy from any other excel or non excel application and paste in these cells it accepts the values and let it paste ignoring the data validation settings. So I added the below code to restrict it to only numbers but this code is allowing decimals as well since the funtion is IsNumeric and blocking only alpha chars. Is there any function which allows only Whole Numbers.
'Only numeric values
Const CELL_ADDRESS = "$A$1:$A$3" 'change range
If Not Application.Intersect(Target, Range(CELL_ADDRESS)) Is Nothing Then
If Not Int(Target.Value) Then
MsgBox "You must enter only numeric values", vbCritical, "Invalid Input"
Target.Value = vbNullString
End If
End If
End Sub
Mar 26 2021 01:24 PM
SolutionTry this:
Private Sub Worksheet_Change(ByVal Target As Range)
'Only numeric values
Const CELL_ADDRESS = "A1:A3" 'change range
Dim rng As Range
Dim f As Boolean
If Not Intersect(Target, Range(CELL_ADDRESS)) Is Nothing Then
For Each rng In Intersect(Target, Range(CELL_ADDRESS))
If Not IsNumeric(rng.Value) Then
f = True
ElseIf Not rng.Value = Int(rng.Value) Then
f = True
End If
If f Then
MsgBox "You must enter only whole numbers!", vbCritical, "Invalid Input"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit For
End If
Next rng
End If
End Sub
Mar 26 2021 01:36 PM
Mar 27 2021 02:29 AM
Mar 27 2021 03:28 AM
You could use
Const CELL_ADDRESS = "D8,G10,L9"
I fear it will be very difficult to make it work 100% the way you want. I recommend instructing users never to use Paste, and only use Paste Values.
To make it easier, you could assign Ctrl+V (^V) to a macro that pastes values only:
Sub PasteValuesOnly()
On Error Resume Next
Selection.PasteSpecial Paste:=xlPasteValues
End Sub
Mar 27 2021 04:50 AM
Mar 27 2021 05:03 AM
Mar 26 2021 01:24 PM
SolutionTry this:
Private Sub Worksheet_Change(ByVal Target As Range)
'Only numeric values
Const CELL_ADDRESS = "A1:A3" 'change range
Dim rng As Range
Dim f As Boolean
If Not Intersect(Target, Range(CELL_ADDRESS)) Is Nothing Then
For Each rng In Intersect(Target, Range(CELL_ADDRESS))
If Not IsNumeric(rng.Value) Then
f = True
ElseIf Not rng.Value = Int(rng.Value) Then
f = True
End If
If f Then
MsgBox "You must enter only whole numbers!", vbCritical, "Invalid Input"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit For
End If
Next rng
End If
End Sub