Function Conflicts...continued

Closed
pc - Apr 16, 2010 at 06:48 AM
rizvisa1
Posts
4479
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

1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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
0
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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.
0