Email validation in Excel user form 1

[benniesanders]

Greetings, I'm not very familiar with VBA, but I have a script I use in ASP to validate email addresses entered from a form. I need something like this for an Excel userform that will verify the format of an email address being entered and I don't know where to start:

if (/^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,3})+$/.test(myForm.email.value)){
return (true)
}
alert("Invalid E-mail Address! Please re-enter.")
myForm.email.focus();
return (false)
}

Any help or thoughts would be appreciated. Many thanks in advance.

 

[Andrzejek]

If you would Google "validate email address VB" you would end up with 1000's hits, first is

http://www.developerfusion.co.uk/show/3350/

Have fun.

---- Andy

 

[benniesanders]

Thanks very much for your help. I'm so used to sharing info in the Active Server Pages forum, I thought I'd check with this sister group first. I'll google VBA.

 

[benniesanders]

If anybody cares, I ended up using this, modifying it for my purposes:

Private Sub CommandButton1_Click() 
If IsValidEmail(TextBox1) Then 
'mail stuff here 
Else 
MsgBox "Not a valid email address." 
End If 
End Sub 


Private Function IsValidEmail(value As String) As Boolean 
Dim RE As Object 
Set RE = CreateObject("vbscript.RegExp") 
RE.Pattern = 
"^[a-zA-Z0-9\._-]+@([a-zA-Z0-9_-]+\.)+([a-zA-Z]{2,3})$" 
IsValidEmail = RE.Test(value) 
Set RE = Nothing 
End Function

works great.

 

[xlbo]

cool - thanks for posting the solution so that others can also benefit

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question