Excel VBA:- simple data validation routing

[SPiercy1978]

I'm trying to use a very simple worksheet event handler procedure which should disallow any non numerical data from being entered into Cell A1. However when I enter text into Cell A1 nothing happens.

Any clues as to why this might be? I'm using Excel 2007.

Here is the event handler code which I created by clicking Worksheet and Change from the drop down menus

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A1$" Then
If Not IsNumeric(Target) Then
MsgBox "Enter a number in Cell A1."
Range("A1").ClearContents
Range("A1").Activate
End If
End If

End Sub


Any help to understand why this is not activating would be highly appreciated.

Regards,

Simon

 

[VoG]

Try

If Target.Address = "$A$1" Then

 

[SPiercy1978]

of course!!! so simple thanks

 

[pedie]

Peter this also works for me
Was just going through...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsNumeric(Range("A1")) Then
MsgBox "Enter a number in Cell A1."
Range("A1").ClearContents
Range("A1").Activate
End If
End Sub