Report

Place new Code to Working Code [Solved]

Ask a question smuneeb 76Posts Saturday September 5, 2015Registration date October 12, 2016 Last seen - Last answered on Jan 1, 2016 11:27AM
Hello,
I would like to request that some line of code (marks as bold) I have to place in the Working Code which is given below
that I cannot figure out where to place.

' New Code for Duplication Entry starts here
If WorksheetFunction.CountIf(Me.Range("C11:C510"), Target.Value) > 1 And Target.Value <> "Applied For" Then
Target.Select
cEdit = MsgBox("You have enter the Enrolment No. <" & Target.Value & "> in <" & ShtName & ">" _
& vbNewLine & "is already exist. Click Ok to remove OR" _
& vbNewLine & "Click Cancel for Correction", vbExclamation + vbOKCancel + vbDefaultButton2, "Duplicate Entry!")
If cEdit = vbOK Then
Target.ClearContents
End If
If cEdit = vbCancel Then
Target.Select
Application.SendKeys "{F2}"
End If
Application.EnableEvents = True
End If
' New Code for Duplication Entry ends here


Working Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("C11:C510")) Is Nothing Then Exit Sub
On Error GoTo ReEnableEvents
Application.EnableEvents = False

Dim regExp As Object
Dim strTarg As String
Dim cEdit As Integer
Dim i As Long
Dim strPattern As String
Dim arrTarg(1 To 4) As String
Dim lngMidChrs As Long
Dim ShtName As String
Dim WrkbookName As String

ShtName = ActiveSheet.Name
WrkbookName = ActiveWorkbook.Name

Set rngCheck = Range("D11:D510")
Set rngBlock = Range("C11:C510")

' New Code for Empty Previous Cell of same Row (Seat No) starts here
If Not Intersect(Target, Me.Range("C11:C510")) Is Nothing Then
If Target.Value = "" Then GoTo ReEnableEvents
If (IsEmpty(Target.Offset(0, -1))) Then
Target.Select
MsgBox "First enter Seat No. in <" & ShtName & ">", vbInformation + vbOKOnly, "Entry Required"
Target.ClearContents
GoTo ReEnableEvents
End If
End If
' New Code for Empty Previous Cell of same Row (Seat No)ends here

'Test if user entered "Applied For" in lieu of Enrol No. code.
'Test in upper case and if correct characters then convert to Proper case"
If UCase(Trim(Target.Value)) = "APPLIED FOR" Then
Target.Value = WorksheetFunction.Proper(Trim(Target.Value))
GoTo ReEnableEvents 'Finished processing because "Applied for" entered
End If

Set regExp = CreateObject("VBScript.RegExp")

'Following line represents: '1st element plus Slash after 1st element
'2nd element plus Slash after 2nd element
'3rd element plus Slash after 3rd element
'4th element

'Convert alpha characters to upper case and remove spaces and slashes (if any)
strTarg = UCase(Replace((Replace(Target.Value, " ", "")), "/", ""))
strPattern = "^[A-Z]{6,7}[0-9]{8}$" 'Pattern to match

If IsAMatch(regExp, strPattern, strTarg) Then
'Insert the slashes in that pattern with 3 alphas in the second block
If Len(strTarg) = 14 Then 'If 3 alpha + 3 alpha + 4 numeric + 4 numeric
Target.Value = Left(strTarg, 3) & "/" & Mid(strTarg, 4, 3) & "/" & Chr(10) _
& Mid(strTarg, 7, 4) & "/" & Mid(strTarg, 11, 4)
Else 'If 3 alpha + 4 alpha + 4 numeric + 4 numeric
'insert the slashes in the pattern with 4 alphas in the second block
Target.Value = Left(strTarg, 3) & "/" & Mid(strTarg, 4, 4) & "/" & Chr(10) _
& Mid(strTarg, 8, 4) & "/" & Mid(strTarg, 12, 4)
GoTo ReEnableEvents
End If

'It will help to remove data if user enter enrolment no as student columns contains "Repeater(s)" Or "Improvement"
If Intersect(Target, rngBlock) Is Nothing Then
GoTo ReEnableEvents ' Do nothing or what you want
Else
If Target.Offset(0, 1) = "Repeater(s)" Or Target.Offset(0, 1) = "Improvement" Then
Target.Select
MsgBox "As you enter <" & Target.Offset(0, 1) & "> in Student's Name Column in <" & ShtName & ">" _
& vbNewLine & "that is why you cannot enter Enrolment No.", vbInformation, "Information"
Target.ClearContents
End If
End If
Application.EnableEvents = True
'ErrHandler:
Else
Target.Select
cEdit = MsgBox("You entered <" & Target & "> is Invalid in <" & ShtName & ">" _
& vbNewLine & "Re-enter the Enrolment No. in one of the following description" _
& vbNewLine & "1. Write only -> applied for <- OR" _
& vbNewLine & "2. First write any 6 Alpha Characters and" _
& vbNewLine & " Second write any 8 Numeric Charaters OR" _
& vbNewLine & "3. First write any 7 Alpha Characters and" _
& vbNewLine & " Second write any 8 Numeric Charaters" _
& vbNewLine & " as provided by Enrolment Section." _
& vbNewLine & "4. Slashes may be omitted during entry." _
& vbNewLine & "Click Ok for Remove Enrolment no OR" _
& vbNewLine & "Click Cancel for Correction", vbCritical + vbOKCancel + vbDefaultButton2, "Invalid Entry!")
If cEdit = vbOK Then
Target.ClearContents
End If
If cEdit = vbCancel Then
Target.Select
Application.SendKeys "{F2}"
End If

ReEnableEvents:
If Err.Number <> 0 Then
MsgBox "Error occurred in Private Sub Worksheet_Change." _
& vbNewLine & "Refer to Administrator Muneeb (KUBS - University of Karachi)" _
& vbNewLine & "of this workbook.", vbCritical, "Error!"
End If
Application.EnableEvents = True
End If
Application.EnableEvents = True
End Sub

Function IsAMatch(regEx As Object, strPatt As String, strToTest As String) As Boolean
'On Error GoTo ReEnableEvents
Dim regMatch As Object ' MatchCollection

With regEx
.Pattern = strPatt
.MultiLine = False
.IgnoreCase = False 'Do NOT IgnoreCase. Set to True to Ignore Case
End With

'Match test string against regEx pattern string
Set regMatch = regEx.Execute(strToTest)
IsAMatch = (regMatch.Count > 0)

Exit Function 'Do not re-enable events here unless error sends code to Sub routine

ReEnableEvents:
If Err.Number <> 0 Then
MsgBox "Error occurred in Function IsAMatch." _
& vbCrLf & "Refer to Administrator of this workbook."
End If
Application.EnableEvents = True
End Function
'--------
Thanks
See more 
Helpful
+3
moins plus
This is one option

Private Function isDuplicate(Target As Range) As Boolean
isDuplicate = False
If Target.Value <> "Applied For" Then
If WorksheetFunction.CountIf(Me.Range("C11:C510"), Target.Value) > 1 Then
Target.Select
cEdit = MsgBox("You have enter the Enrolment No. <" & Target.Value & "> in <" & ShtName & ">" _
& vbNewLine & "is already exist. Click Ok to remove OR" _
& vbNewLine & "Click Cancel for Correction", vbExclamation + vbOKCancel + vbDefaultButton2, "Duplicate Entry!")

isDuplicate = True
Select Case cEdit
Case Is = vbOK
Target.ClearContents
Case Is = vbCancel
Target.Select
Application.SendKeys "{F2}"
End Select
End If
End If
' New Code for Duplication Entry ends here
End Function

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Count > 1) Then
'too many cells are selected. your code is not supporting that
Exit Sub
End If

If Intersect(Target, Me.Range("C11:C510")) Is Nothing Then Exit Sub

On Error GoTo ReEnableEvents
Application.EnableEvents = False

Dim regExp As Object
Dim strTarg As String
Dim cEdit As Integer
Dim i As Long
Dim strPattern As String
Dim arrTarg(1 To 4) As String
Dim lngMidChrs As Long
Dim ShtName As String
Dim WrkbookName As String

ShtName = ActiveSheet.Name
WrkbookName = ActiveWorkbook.Name

Set rngCheck = Range("D11:D510")
Set rngBlock = Range("C11:C510")

' New Code for Empty Previous Cell of same Row (Seat No) starts here
If Not Intersect(Target, Me.Range("C11:C510")) Is Nothing Then
If Target.Value = "" Then GoTo ReEnableEvents
If (IsEmpty(Target.Offset(0, -1))) Then
Target.Select
MsgBox "First enter Seat No. in <" & ShtName & ">", vbInformation + vbOKOnly, "Entry Required"
Target.ClearContents
GoTo ReEnableEvents
End If
End If

' New Code for Empty Previous Cell of same Row (Seat No)ends here

'Test if user entered "Applied For" in lieu of Enrol No. code.
'Test in upper case and if correct characters then convert to Proper case"
If UCase(Trim(Target.Value)) = "APPLIED FOR" Then
Target.Value = WorksheetFunction.Proper(Trim(Target.Value))
GoTo ReEnableEvents 'Finished processing because "Applied for" entered

ElseIf (isDuplicate(Target)) Then
GoTo ReEnableEvents
End If

Set regExp = CreateObject("VBScript.RegExp")

'Following line represents: '1st element plus Slash after 1st element
'2nd element plus Slash after 2nd element
'3rd element plus Slash after 3rd element
'4th element

'Convert alpha characters to upper case and remove spaces and slashes (if any)
strTarg = UCase(Replace((Replace(Target.Value, " ", "")), "/", ""))
strPattern = "^[A-Z]{6,7}[0-9]{8}$" 'Pattern to match

If IsAMatch(regExp, strPattern, strTarg) Then
'Insert the slashes in that pattern with 3 alphas in the second block
If Len(strTarg) = 14 Then 'If 3 alpha + 3 alpha + 4 numeric + 4 numeric
Target.Value = Left(strTarg, 3) & "/" & Mid(strTarg, 4, 3) & "/" & Chr(10) _
& Mid(strTarg, 7, 4) & "/" & Mid(strTarg, 11, 4)
Else 'If 3 alpha + 4 alpha + 4 numeric + 4 numeric
'insert the slashes in the pattern with 4 alphas in the second block
Target.Value = Left(strTarg, 3) & "/" & Mid(strTarg, 4, 4) & "/" & Chr(10) _
& Mid(strTarg, 8, 4) & "/" & Mid(strTarg, 12, 4)
GoTo ReEnableEvents
End If

'It will help to remove data if user enter enrolment no as student columns contains "Repeater(s)" Or "Improvement"
If Intersect(Target, rngBlock) Is Nothing Then
GoTo ReEnableEvents ' Do nothing or what you want
Else
If Target.Offset(0, 1) = "Repeater(s)" Or Target.Offset(0, 1) = "Improvement" Then
Target.Select
MsgBox "As you enter <" & Target.Offset(0, 1) & "> in Student's Name Column in <" & ShtName & ">" _
& vbNewLine & "that is why you cannot enter Enrolment No.", vbInformation, "Information"
Target.ClearContents
End If
End If
Application.EnableEvents = True
'ErrHandler:
Else
Target.Select
cEdit = MsgBox("You entered <" & Target & "> is Invalid in <" & ShtName & ">" _
& vbNewLine & "Re-enter the Enrolment No. in one of the following description" _
& vbNewLine & "1. Write only -> applied for <- OR" _
& vbNewLine & "2. First write any 6 Alpha Characters and" _
& vbNewLine & " Second write any 8 Numeric Charaters OR" _
& vbNewLine & "3. First write any 7 Alpha Characters and" _
& vbNewLine & " Second write any 8 Numeric Charaters" _
& vbNewLine & " as provided by Enrolment Section." _
& vbNewLine & "4. Slashes may be omitted during entry." _
& vbNewLine & "Click Ok for Remove Enrolment no OR" _
& vbNewLine & "Click Cancel for Correction", vbCritical + vbOKCancel + vbDefaultButton2, "Invalid Entry!")
If cEdit = vbOK Then
Target.ClearContents
End If
If cEdit = vbCancel Then
Target.Select
Application.SendKeys "{F2}"
End If

ReEnableEvents:
If Err.Number <> 0 Then
MsgBox "Error occurred in Private Sub Worksheet_Change." _
& vbNewLine & "Refer to Administrator Muneeb (KUBS - University of Karachi)" _
& vbNewLine & "of this workbook.", vbCritical, "Error!"
End If
Application.EnableEvents = True
End If
Application.EnableEvents = True
End Sub

Function IsAMatch(regEx As Object, strPatt As String, strToTest As String) As Boolean
'On Error GoTo ReEnableEvents
Dim regMatch As Object ' MatchCollection

With regEx
.Pattern = strPatt
.MultiLine = False
.IgnoreCase = False 'Do NOT IgnoreCase. Set to True to Ignore Case
End With

'Match test string against regEx pattern string
Set regMatch = regEx.Execute(strToTest)
IsAMatch = (regMatch.Count > 0)

Exit Function 'Do not re-enable events here unless error sends code to Sub routine

ReEnableEvents:
If Err.Number <> 0 Then
MsgBox "Error occurred in Function IsAMatch." _
& vbCrLf & "Refer to Administrator of this workbook."
End If
Application.EnableEvents = True
End Function

smuneeb 76Posts Saturday September 5, 2015Registration date October 12, 2016 Last seen - Jan 1, 2016 11:27AM
Hi rizvisa1
First of all I would like to apologize for late reply. Reason I was ill.
and Your code is perfectly work
I am very grateful, I have no words for Thanks
Regards
Reply
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!