Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
for Email address format
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
for Email address format
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 "Lakshmanagm" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
for Email address format
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 "Mike H" wrote: 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 "Lakshmanagm" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format cell content as hyperlinked email address | Excel Discussion (Misc queries) | |||
send wkbk as an email attachment with an email address copied from | Excel Discussion (Misc queries) | |||
can I copy a column of email addresses, paste into email address? | New Users to Excel | |||
Transfer Email addresses from spreadsheet to email address book | Excel Discussion (Misc queries) | |||
How to format a cell as email address | Excel Discussion (Misc queries) |