Allow only email address format in Excel cell

[lampman]

How do I allow only email address format to be entered into an Excel 2007 cell?

 

[Ron Coderre]

Here's one approach...

• ALT+F11…to open the VBA editor
• Insert.Module…to create a new module for the current workbook
• Copy the below code and paste it into that module:

‘ --- beginning of code ---
Function IsValidEmail(mytext As String) As Boolean
Dim RE As Object
Dim REPattern As String
Dim REMatches As Object
 
Set RE = CreateObject("vbscript.regexp")
REPattern = "^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)$"
RE.Global = True
RE.ignorecase = True
RE.Pattern = REPattern
 
Set REMatches = RE.Execute(mytext)
 
IsValidEmail = (REMatches.Count > 0)
 
End Function
‘ --- end of code ---

Technically, you could use that User Defined Function (UDF) this way:
B1: IsValidEmail(A1)

If A1 contains a valid email address, the function returns: True
Otherwise, it returns: False

However, to prevent the user from entering invalid email addresses:
• Right-click on the sheet tab...Select: View Code (to open the VBA module for that sheet)

• Copy the below code and paste it into that module:

‘ --- beginning of code ---
Private Sub Worksheet_Change(ByVal Target As Range)
Dim response
If Not Intersect([A1], Target) Is Nothing Then
'A1 has been changed
If Not WorksheetFunction.CountA(Target) = 0 Then
'The cell is not blank
If Not IsValidEmail(Target.Text) Then
'the contents are not an email address
MsgBox Title:="Invalid entry", Prompt:="Only valid email addresses allowed.", Buttons:=vbCritical + vbOKOnly
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End If
End Sub
‘ --- end of code ---

Now, if a user enters an invalid email address in cell A1
- a message box will pop up notifying the user
- the entry will be erased

Is that something you can work with

 

[lampman]

Thanks for the quick response, Ron! I'll give it a try. First, though, am I correct that this solution would only work A1 as you have written it? I need to apply it to several cells in a column.

 

[lampman]

nm, Ron, I figured it out (replaced "A1" with "E4..E50"). Works great so far! Thanks again!

 

[Ron Coderre]

Glad I could help.

 

[lampman]

Do you happen to know the answer to this post too? http://www.mrexcel.com/forum/showthread.php?t=613841

 

[tothezenith]

Thankyou too, very helpful. I have an additional query regarding this however, I can't figure out how to word my coding to:

A) reference cell is a public constant
B) multiple seperate target cells.

Can you help?

 

[SalesManagerChris]

Guys I need some help on this.

I have a workbook we use thats taken me a few years worth of development in several business to process our vehicle sales. It saves a whole load of information into a database text file and it can fill the fields back in from the database.

Im trying to implement this valid email code into the form but it keeps returning me the error "Invalid use of Null" debug.

im not sure quite why it does this. and i cant seem to get it to skip if the value is NULL

I think its because my macro that re-fills the worksheet forces a NULL entry into the field before it moves on and i cant change that as it has to overwrite other pre filled boxes to keep consistancy of data.

any suggestions to fix this error??

thanks chaps.