I don't think you tried the code in today's response.

art wrote:

Yes, but as I said it does not delete the amount entered if it is not a
wholle number.

"Dave Peterson" wrote:

Did you try the code?

art wrote:

Yes. And I think I will have to use that.

Thanks Anyways.

"Dave Peterson" wrote:

That's why I wrote this note:

This routine doesn't show a message. It just makes the value an integer:

Did you even try the data|validation?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim myCell As Range
Dim myIntersect As Range

If Target.Cells.Count 1 Then
Exit Sub 'only one cell at a time
End If

Set myRng = Me.Range("a:a,c3:d9,x1:z1, w33")

Set myIntersect = Intersect(Target, myRng)

If myIntersect Is Nothing Then
'do nothing
Else
With Target
If IsError(.Value) Then
'skip it
Else
If IsNumeric(.Value) Then
If Int(.Value) = .Value Then
'ok as it is.
Else
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Invalid!"
End If
End If
End If
End With
End If

End Sub



art wrote:

It doesn't delete the entry if the user entered .5 or 1.3.... It just rounds
it.

I just need the code to cancel the entry the user enters when it is not a
whole number. Please give me this code. Thanks.

"Dave Peterson" wrote:

So why not select the huge amount of cells and apply the same data|validation
rules to those cells?

This routine doesn't show a message. It just makes the value an integer:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim myCell As Range
Dim myIntersect As Range

Set myRng = Me.Range("a:a,c3:d9,x1:z1, w33")

Set myIntersect = Intersect(Target, myRng)

If myIntersect Is Nothing Then
'do nothing
Else
For Each myCell In myIntersect.Cells
With myCell
If IsError(.Value) Then
'skip it
Else
If IsNumeric(.Value) Then
If Int(.Value) = .Value Then
'ok as it is.
Else
Application.EnableEvents = False
.Value = Int(.Value)
Application.EnableEvents = True
End If
End If
End If
End With
Next myCell
End If

End Sub

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste it into the code window.

You'll have to change the range to match your requirements.

art wrote:

i new about this. but i need to put it in a huge amount of cells and besids i
need it in vba code please. can you please help me with this. thanks.

"Dave Peterson" wrote:

Why use VBA?

You could accomplish the same thing by using Data|Validation. Specify whole
numbers and you're done.

art wrote:

Hello:

I would like to set a given range to NumberFormat to "0" (No digits), and if
the user enters not a whole number, either .3 or even together with a whole
number like 1.4 then it should clear out the values entered pop up a msgbox
"no half numbers allowed..."
How can I do that in VBA.

Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson