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.

What about using Data Validation? Try this. Select the cell (or cells) you
want to have this functionality and click Data/Validation on the menu bar.
Click the Setting tab on the dialog box that appears, select Custom from the
Allow drop-down and copy/paste the following into Formula field...

=A1=INT(A1)

You can customize the messages that will appear by clicking the Input
Message and Error Alert tabs.

--
Rick (MVP - Excel)


"art" wrote in message
...
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.

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

Well, almost done<g... you must also specify a minimum and maximum value
(the reason I offered the approach I did was to avoid having to specify
them).

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
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

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

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

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

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

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

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

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

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

I'm sorry, your right. Looks like I changed something by so it didn't work as
I wanted. Now it works good. Thanks. The pop up message comes up, "Invalid".
Thanks for your help. I knew that "Dave Peterson" knows what he is talking
about. Thanks again.




"Dave Peterson" wrote:

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

Glad you got it working.

art wrote:

I'm sorry, your right. Looks like I changed something by so it didn't work as
I wanted. Now it works good. Thanks. The pop up message comes up, "Invalid".
Thanks for your help. I knew that "Dave Peterson" knows what he is talking
about. Thanks again.

"Dave Peterson" wrote:

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


--

Dave Peterson