Number Format

[art]

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.

 

[Rick Rothstein]

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.

 

[Dave Peterson]

Why use VBA?

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

 

[Rick Rothstein]

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).

 

[art]

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]

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]

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]

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

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.

 

[art]

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

Thanks Anyways.

 

[Dave Peterson]

Did you try the code?

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

Thanks Anyways.

 

[art]

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

 

[Dave Peterson]

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

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

 

[art]

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]

Glad you got it working.

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.