Validate

Solved/Closed
pc - Apr 20, 2010 at 02:53 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 20, 2010 at 06:07 AM


Hey frnds

I almost have a same problem:

I need to validate columns for alphanumeric and email format.below is the sequence of my code:

Created two Subs in "Module2" like below :

Public Sub alpha(ByVal Target As Range)
cellNeedValidation = "A2:A10"
'if more cells need validation, on right side of "cellNeedValidation =" put "A1,C3,D7"
If Not Intersect(Target, Range(cellNeedValidation)) Is Nothing Then
For g = 1 To Len(Target.Value)
testchar = Asc(Mid(Target.Value, g, 1))
flg = 0
If testchar > 47 And testchar < 58 Then
flg = 1
ElseIf testchar > 64 And testchar < 91 Then
flg = 1
ElseIf testchar > 96 And testchar < 123 Then
flg = 1
End If
If flg = 0 Then
MsgBox "Your entry contains invalid character." & Chr(13) & _
"Character allowed are a-z, A-Z & 0-9.", vbOKOnly, "Invalid Character"
Target.Select
Application.SendKeys "{f2}+^{home}"
flg = 0
Exit For
End If
Next
End If
End Sub

Public Sub email(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("B2:B10")) Is Nothing Then
Dim RE As Object
Set RE = CreateObject("vbscript.REgExp")
RE.Pattern = "^[a-zA-Z0-9\._-]+@([a-zA-Z0-9_-]+\.)+([a-zA-Z]{2,3})$"
If RE.test(Target.Value) = False Then

MsgBox "Invalid email." & Chr(13) & _
"Character allowed are a-z, A-Z & 0-9.", vbOKOnly, "Invalid Character"
Target.Select
Application.SendKeys "{f2}+^{home}"
End If
Set RE = Nothing
End If
End Sub

==========================================
Created a macro in "Module1" like below and calling Subs in macro :

Sub Macroemailalpha()
'
' Macroemailalpha Macro
' Macro recorded 4/20/2010 by aaig
''
Call alpha(Target)
Call email(Target)

End Sub

=========================================
Calling Macro in Sheet1 like below :

Private Sub Worksheet_Change(ByVal Target As Range)
Call Macroemailalpha
End Sub


But not working
Please help anyone.

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 20, 2010 at 06:07 AM
what is the issue?
0