Function Conflicts...continued

Closed
pc - Apr 16, 2010 at 06:48 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 16, 2010 at 07:32 AM
hiii
there was some misconception m again asking my question
i have some columns in excel sheet out of which i have to write macros to validate them

Card Number(shud be alphanumeric)
email(validated as email format)

Private Sub Worksheet_Change(ByVal Target As Range)
cellNeedValidation = "B2:B10"
'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

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("C2:C100")) 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

bt the problem is there can be only one Worksheet_Change event in one workbook(as i know).i jst want to know how to run these two macros so that both will work

Related:

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 16, 2010 at 07:01 AM
Why not make a call to macro from the event

lets say you rename first as Mac1
and 2nd as mac2

then in event you can call it like

Private Sub Worksheet_Change(ByVal Target As Range)
call mac1(Target )
call mac2(target)
end sub
what shud be the definition of
mac1(Target)
mac2(Target)

i have called it same ways but
in mac1(target) is giving empty value when debugging.

the whole code is


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

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

Sub addtwo()
alpha (Target)
email (Target)
End Sub
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 16, 2010 at 07:32 AM
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.