Quick VB Help (email validation)

JimmyG

Board Regular
Joined
Aug 16, 2005
Messages
70
Hi there!

I'm sure its an easy one - but I'm stuck....

I have an Excel questionnaire "form". When a user enters a particular value, they spring to another cell, for example:

Code:
Private Sub worksheet_change(ByVal Target As Range)

If Target.Address = Range("title").Address Then
    Select Case Target.value
        Case "Other..."
            Range("title_other").Select
        Case Else
            Range("firstname").Select
     End Select
End If

The values in cell "title" are controlled via a validation list.
If value "Mr.", "Mrs.", "Dr." etc, then go straight to "firstname"
If value "Other...", then go to cell "title_other"



So far so good. But now I want to make it a little more complex...

Code:
If Target.Address = Range("email").Address Then
Range("mobile").Select
End If

When someone enters an email, I want to check if it is in a valid format first, before springing to field "mobile".

In cell "email_valid" I have the following formula:

Code:
=IF(ISBLANK(email),TRUE,IsValidEmail(email))

IsValidEmail() is a user-defined formula (using a regular expression).
Currently, when the formula in cell "email_valid" evaluates to FALSE, then, via conditional formatting, the font in "email" turns red.

If "email_valid" is FALSE, I don't want to spring from cell "email" to cell "mobile", but to stay in cell "email".

I thought something like this would do the trick....

Code:
If Target.Address = Range("email").Address Then
    Range("email_valid").Select
        Select Case Target.value
            Case "FALSE"
                Range("email").Select
            Case Else
                Range("mobile").Select
        End Select
End If

... but it doesn't work! Any suggestions?

Thanks, JimmyG (VB-novice!)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Why bother with VBA to move through a Form, use Sheet protection. If you Lock all cells except input then protect the sheet the user can tab through it.

Here's a UDF that checkan email address for validity, place it in a Standard Module

Code:
Option Explicit


Public Function ValidEmail(pAddress As String) As Boolean
     '-----------------------------------------------------------------
    Dim oRegEx As Object
    Set oRegEx = CreateObject("VBScript.RegExp")
    With oRegEx
        .Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
        ValidEmail = .Test(pAddress)
    End With
    Set oRegEx = Nothing
End Function

use it in a formula

=IF(ValidEmail(A1),"","Invalid email")

Or you could use it in Coditional Formatting, place this in the Formula is box and choose a Format to highlight it. Normally I colour input cells, so I would choose white to highlight an invalid entry.

=ValidEmail(A1)
 
Upvote 0
VB to spring through form

Hi Roy,

Thanks for the response.

I'm already using an [identical] UDF for the email validition, and also for German, Irish or UK number plates. Thanks anyway.

The reason for using VB to move from one cell to another is that when users enters the value "Other..." they jump to a different cell than had they entered a standard value....

Pull-Down list:
Mr.
Mrs.
Ms.
Dr.
Other...

If "Other...." is selected, goto cell H3 else goto D5
In H3 users can type in "non-validated" values, and then continue to D5


Regards, JimmyG

ps how do I add a screenshot??

[/img]
 
Upvote 0
I'm not sure how you add a screen shot here, I think maybe using the HTML addin.

I can't remember where I got the UDF from, but it's stored in my code library.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top