Mar 26 2021 12:49 PM
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
Mar 26 2021 01:24 PMSolution
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