VBA Textbox email Validation

[WRXFRENZY]

Good Morning/Evening All,

I am trying to place validation with a forms textbox to check that a valid email address is entered eg: ###@###.### or similar

I copied this code from a previous post and slighty modified but can't see whats wrong, I don't get an error but at the same point I don't get any response:

The code I have agaist the textbox is as follows: My Textbox is TxtEmail1

Private Sub TxtEmail1_Click()
If IsValidEmail(TxtEmail1) 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

Any ideas what's up ? Thanks Heaps.

Cheers

Eric

 

[jindon]

Hi
Have you tried

    If IsValidEmail(TxtEmail1.Value) Then

 

[WRXFRENZY]

Howdy Jindon,

Thank You for your response to my dilema: I did try as you suggested .Value and still no different ? It's got me why it does not work ? I even tried changing from Click() to Change()

Private Sub TxtEmail1_Click()
to
Private Sub TxtEmail1_Change() thinking if I change Value form Blank it may work but nope...............

Any other ideas or suggestions ?

Cheers Eric

 

[jindon]

OK
can you try this?

Private Sub TxtEmail1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With CreateObject("vbscript.regexp")
     .Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
     If .test(TxtEmail1.Value) Then
          MsgBox "OK"
     Else
          MsgBox "No good"
          Cancel = True
     End If
End With
End Sub

 

[WRXFRENZY]

Thankyou Jindon

I tried your code and it works great, thaks for that !!! Legend

Cheers Eric

 

[ganeshvenkatram84]

Hi Jindon

Your code works absolutely fine. But in some cases the code is not working. For eg <TABLE style="WIDTH: 188pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=250 border=0 x:str><COLGROUP><COL style="WIDTH: 188pt; mso-width-source: userset; mso-width-alt: 9142" width=250><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 188pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=250 height=17 x:str="'amrit.boricha@gmail.com.com">amrit.boricha@gmail.com.com</TD></TR></TBODY></TABLE>

Please Assist ASAP

 

[destboul]

Hello,

Try this little correction to avoid mystake on email adress with more than one period after "@" :

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With CreateObject("vbscript.regexp")
'.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,4}$"
.Pattern = "^[\w-\.]+@[\w-]+\.+[A-Za-z]{2,4}$"
If .test(TextBox1.Value) Then
MsgBox "OK"
Else
MsgBox "No good"
Cancel = True
End If
End With
End Sub

 

[Anupkb]

Works great. But can you let me know how to allow multiple email addresses in same text box? They will be separated by ; symbol

Hello,

Try this little correction to avoid mystake on email adress with more than one period after "@" :

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With CreateObject("vbscript.regexp")
'.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,4}$"
.Pattern = "^[\w-\.]+@[\w-]+\.+[A-Za-z]{2,4}$"
If .test(TextBox1.Value) Then
MsgBox "OK"
Else
MsgBox "No good"
Cancel = True
End If
End With
End Sub