macro for validating and highlight email ID wrongly formated.

[mukeshpgdm]

Dear All,

I have been using excel form last 1 year. I do have good knowledge for macro but for validating emailID entry I am not getting success.,So if any body can give me a sample code for validating email ID entry or a macro for checking and highlight those email ID which r wrongly formated..

Can anybody Help me.

Thanking All
Mukesh

[mudraker]

Can you give examples of what you consider correctly & incorettly formated email ID's

[mukeshpgdm]

Can you give examples of what you consider correctly & incorettly formated email ID's

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Dear Mr.mudraker sir
What i am trying to do is written here above .so please evaluate it and provide me Guidance
to solve my problem.this code is working well in Ms-Access.

Thanks and regrads
Mukesh

[mudraker]

In your code you refer to Email_Pattern_1.SetFocus several times.

What is Email_Pattern_1 & how is it setup? I am assuming that as you have used setfocus it is an object on a form.

[mudraker]

I can see what appear to be several errors in your code.

You have posted code with 2 End Functions listed when there should only be one.

In Emailvalidate you have a msgbox just before the End Sub. This should be inside an if - else - end if statement

Your validation function is validating some charaters that I beleive it should not be - here are some characters that were being approved
: ; < = > ?.[\]^_`
I have changed the function so that it no longer validates these characters.

When testing a number that is not going to be a negative I suggest you use

instead of using
If i <> 0 Then
use I
If i > 0 Then

I also suggest that instead of setting validate to true at the start of the function and then seeting it to false when it fails set validate to false at the start and only set it to true once it has passed all tests. This is what I have done in the version I have attached.

Attached is the 2 macros modified to give the results you are after
Note :- After modifying the macros I run the macro testing for various errors - It is possible I have overlooked something.

Please Login or Register  to view this content.

[mukeshpgdm]

I can see what appear to be several errors in your code.

You have posted code with 2 End Functions listed when there should only be one.

In Emailvalidate you have a msgbox just before the End Sub. This should be inside an if - else - end if statement

Your validation function is validating some charaters that I beleive it should not be - here are some characters that were being approved
: ; < = > ?.[\]^_`
I have changed the function so that it no longer validates these characters.

When testing a number that is not going to be a negative I suggest you use

instead of using
If i <> 0 Then
use I
If i > 0 Then

I also suggest that instead of setting validate to true at the start of the function and then seeting it to false when it fails set validate to false at the start and only set it to true once it has passed all tests. This is what I have done in the version I have attached.

Attached is the 2 macros modified to give the results you are after
Note :- After modifying the macros I run the macro testing for various errors - It is possible I have overlooked something.

Please Login or Register  to view this content.

Dear Sir,
Thanks a lot.
Thanks for ur support,but sir this does not validate if a emailid contains two @
e.g."mukesh@@sify.com",what to do now?

Sir One thing more with function 'validate' when u changed validate= false at start it and validate=false at the end.,it does not wrk,.I have changed it .at start made it true and at end false.and it works.it check the entry....

Sir, can I use any formating to those email wrongly formated and at a whole column .IS it possible?

sir u r right (regarding email_pattern.setfocus)I m using a textbox in a form of access where I validate user to enter correct emailid and if user fails it must focus to that emailID_pattern text box .

Once again thanks sir.

Thanks and Regards
mukesh

[mudraker]

I thank you for being polite but please drop the Sir.

I had a typing error in the validate function. I had a capital Z when it should have been a lower case z in. - Corrected code at the end of this message.

re your comment
Thanks for ur support,but sir this does not validate if a emailid contains two @
e.g."mukesh@@sify.com"

The validation function rejects the email address if there are 2 @ next to each other. Is this what you want or do you want it to approve it.

The validation function also allows the email address to have multiple @ as long as they are not next to the 1st @

Rejects "mukesh@@sify.com"
Allows "muk@esh@sify.com"
Allows "muk@esh@@sify.com"


The Emailvalidate macro can be modified to check each entry in a column.
I suggest we look at that part after we get the validation function working correctly.

Amended validation function code

Please Login or Register  to view this content.

[mukeshpgdm]

I thank you for being polite but please drop the Sir.

I had a typing error in the validate function. I had a capital Z when it should have been a lower case z in. - Corrected code at the end of this message.

re your comment
Thanks for ur support,but sir this does not validate if a emailid contains two @
e.g."mukesh@@sify.com"

The validation function rejects the email address if there are 2 @ next to each other. Is this what you want or do you want it to approve it.

The validation function also allows the email address to have multiple @ as long as they are not next to the 1st @

Rejects "mukesh@@sify.com"
Allows "muk@esh@sify.com"
Allows "muk@esh@@sify.com"


The Emailvalidate macro can be modified to check each entry in a column.
I suggest we look at that part after we get the validation function working correctly.

Amended validation function code

Please Login or Register  to view this content.

Thanks
I only need One @ in an email ID nothing else.
thanks and regards
mukesh

[mudraker]

Re you request for a loop to chack all entries in a column

You could use any of these types of loops
Do Loop
While Wend
For Next
For Each

I prefer to use For Next or For Each type loops & is what I have added to the following Emailvalidate code
You will need to change the 3 Red column A's to what ever column has the e-mail address.
I have assumed that your 1st email address is listed in row 2 If it is not then you will need to change the Red 2 refrence to the correct row number

I have added several new variables
I have moved all the msgbox commands down to one new If statement

The msgbox command in blue will pop up if you have a blank cell within email address column. I left that as I did not know how you want to handle a blank cell.

With all the changes I made there has been no changes to the validation logic you originally coded.

There is no changes required to the validation function

Please Login or Register  to view this content.

[mukeshpgdm]

Re you request for a loop to chack all entries in a column

You could use any of these types of loops
Do Loop
While Wend
For Next
For Each

I prefer to use For Next or For Each type loops & is what I have added to the following Emailvalidate code
You will need to change the 3 Red column A's to what ever column has the e-mail address.
I have assumed that your 1st email address is listed in row 2 If it is not then you will need to change the Red 2 refrence to the correct row number

I have added several new variables
I have moved all the msgbox commands down to one new If statement

The msgbox command in blue will pop up if you have a blank cell within email address column. I left that as I did not know how you want to handle a blank cell.

With all the changes I made there has been no changes to the validation logic you originally coded.

There is no changes required to the validation function

Please Login or Register  to view this content.

Dear Sir,

Thanks again. Current code is working well sir.Now there is difficulty.but as suggested by u about multiple option for loop I will try all sir.

sorry for mailing u a private mail.

Thanks and regards
mukesh

[mudraker]

Glad to hear the e-mail validation's sorted out.

I assume when you refer to difficulty & loops you are refering to another part of your project.

[Chitranjan]

Hi.. Pls check for this code.

Sub EmailFormat()

Dim LastRow As Long
Dim FullName As String

LastRow = UsedRange.Rows.Count 'or some other way of determining the last row used

Dim ReturnValue As Boolean

LastRow = 0
LastRow = UsedRange.Rows.Count 'or some other way of determining the last row used
For l = 3 To LastRow 'loop through all used rows
ReturnValue = IsEmailAddress(ActiveSheet.Cells(l, 4).Value) 'Cell D1
If ReturnValue = False Then
ActiveSheet.Cells(l, 4).Interior.ColorIndex = 3 ' 3 denotes Red
'MsgBox ActiveSheet.Cells(i, 1).Value & " : Length Limit should not exceed 10."
Else
ActiveSheet.Cells(l, 4).Interior.ColorIndex = 0 ' 0 denotes white
End If
Next l

End Sub

Function IsEmailAddress(txt As String) As Boolean
'Code by Jindon MrExcel.com MVP
With CreateObject("VBScript.RegExp")
.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
IsEmailAddress = .test(txt)
End With
End Function

Hope this helps

[AB33]

Chit,
Welcome to the forum!
Please take a moment to read forum's rule, particularly on how to use tags with your code.