Forum Discussion
Data Validation to force user to add correct email address
Hello
I have a large spread sheet which includes 100's of emails, often if the email address is unknown someone will write random text which is quite frustrating, I have tried using custom data validation to stop someone from being able to enter text which doesn't include a @ but I'm getting a bit stuck. Can anyone advise me of the correct formula I should be using.
Thanks in advance.
2 Replies
Please try the following in data validation custom formula
=ISNUMBER(MATCH("*@*.???",C2,0))
Ps: Please replace C2 with the cell reference of the first row of email list
Hi Sarah,
you can use the following userdefined function. See attached file too.
Function Mailcheck(strText As String) As Boolean
Dim objRegEx As Object
Dim objMatch As Object
Set objRegEx = CreateObject("vbscript.regexp")
With objRegEx
.Global = False
.Pattern = "\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" 'gängige e-Mail-Adressen prüfen
.MultiLine = True
Set objMatch = .Execute(strText)
If objMatch.Count <> 0 Then
Mailcheck = True
Else
Mailcheck = False
End If
End With
Set objRegEx = Nothing
Set objMatch = Nothing
End FunctionBest regards
Bernd
https://vba-tanker.com/
