for Email address format

[Lakshmanagm]

in excel sheet one of the column is Email. i wnat to put
conditions/validations/functions to that email cell for it does not allowing
the wrong email format id's --like only text or only numericals or etc..(
wrong email format).
i.e if typed or copied the invalid email format id's i want to put alert
massage . Please tell me how it is possible.

 

[Mike H]

Hi,

Here's one way and this works on column A so change to suit. Right click
your sheet tab, view code and paste this in. Try entering valid and invalid
emaill addresses in column A

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
With CreateObject("VBScript.RegExp")
.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
If Not .test(Target.Value) Then
MsgBox Target.Value & " is not a valid Email"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End With
End If
End Sub

Mike

 

[Mike H]

Hi,

I should have trapped for empty or multiple cells so try this instead

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
With CreateObject("VBScript.RegExp")
.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
If Not .test(Target.Value) Then
MsgBox Target.Value & " is not a valid Email"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End With
End If
End Sub

Mike

Hi,

Here's one way and this works on column A so change to suit. Right click
your sheet tab, view code and paste this in. Try entering valid and invalid
emaill addresses in column A

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
With CreateObject("VBScript.RegExp")
.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
If Not .test(Target.Value) Then
MsgBox Target.Value & " is not a valid Email"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End With
End If
End Sub

Mike

in excel sheet one of the column is Email. i wnat to put
conditions/validations/functions to that email cell for it does not allowing
the wrong email format id's --like only text or only numericals or etc..(
wrong email format).
i.e if typed or copied the invalid email format id's i want to put alert
massage . Please tell me how it is possible.