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:
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...
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:
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....
... but it doesn't work! Any suggestions?
Thanks, JimmyG (VB-novice!)
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!)