VBA OnlyNumber() for cell

[Excelnoub]

Good day everyone,

I know there is a VBA for TextBox (OnlyNumbers) but is there a VB code that will do the same thing but for cells...


Example:

[pre]

Code
Private Sub OnlyNumbers()
If TypeName(Me.ActiveControl) = "TextBox" Then
With Me.ActiveControl
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Sorry, only numbers allowed."
.Value = vbNullString
End If
End With
End If
End Sub

[/pre]
Code

I am trying to come up with a code in my Target, Range("D5:D30") to have numbers only and if there is a letter then the msg error will say MsgBox "Sorry, only numbers allowed."


Is this possible or I need to use another alternative ie: Data Validation?


Thank you...


Excelnoub

 

[DaveTurton]

put the below in the sheet code (right click sheet tab and select view code)

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row >= 5 And Target.Row <= 30 And Target.Column = 4 Then
If Not IsNumeric(Target.Value) Then
MsgBox "Sorry, only numbers allowed."
Target.Value = vbNullString
End If
End If
End Sub

[/pre]

 

[Excelnoub]

That was quick and awsome.


Thank you Dave