VBA Userform textbox validation

[rayzgurl]

I'm having an issue with people not putting a valid email address in
text box on a user form. They are putting just the person name, leavin
off .com, putting spaces in where they don't belong etc...etc... It'
imporant that they use a valid email address because the form generate
an email that is sent to the email address they have put in the form
If Outlook doesn't recognize it as a valid email address, it locks u
and everything shuts down. Can someone help me with a code to validat
that what they are entering is a valid, at least in format, emai
address.

Thanks,

Pa

 

[Frank Kabel]

Hi Pam
one way: Enter the following code in one of your workbook modules

Public Function MailaddressOK(Adresse As String) As Boolean
Dim oVScriptRegEx As Object
Set oVScriptRegEx = CreateObject("VBScript.RegExp")
With oVScriptRegEx
.Pattern = "^\w+((-\w+)|(\.\w+))*\@\w+((\.|-)\w+)*\.\w+$"
MailaddressOK = .test(Adresse)
End With
End Function

you may have to register the VBSCRIPT object first in the VBE to use
regular expressions.

Now use this function to validate your text entries for valid email
addresses

 

[rayzgurl]

You could use a Regular Expression to check the validity of the textbo
entry.

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 Functio