Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, S, bTest As Boolean
Dim Phong_Loai, phong&, NV$, tmp
Dim fRow&, eRow&, fCol&, eCol&, iR&, jC&, i&, j&, ik&
Const rGiamThi As String = "J4:N35" 'Dia chi Nhap lieu Giam Thi
Const SoPhong As Long = 16 'So phong thi
Const rGiamSat As String = "J36:N40" 'Dia chi Nhap lieu Giam Sat
Const SoPhieuGS As Long = 5 'So phieu boc tham GS
If Target.Count > 1 Then Exit Sub 'Chi xet nhap 1 cell
If Not Intersect(Target, Range(rGiamThi)) Is Nothing Then
Set Rng = Range(rGiamThi)
Phong_Loai = Target.Value
If Phong_Loai = Empty Then Exit Sub
bTest = False
If InStr(1, Phong_Loai, ".") > 0 Then
S = Split(Phong_Loai, ".")
If IsNumeric(S(0)) And (S(1) = "1" Or S(1) = "2") Then
phong = CLng(S(0)) 'STT Phong thi
Phong_Loai = phong & "." & S(1)
If phong >= 1 And phong <= SoPhong Then bTest = True
End If
End If
Call TangToc(False)
Target.Select
If bTest = False Then
MsgBox ("Nhap sai Ma Phong_Nhom Giam thi")
Target.Value = Empty '***
Call TangToc(True)
Exit Sub
End If
fRow = Rng.Row: eRow = fRow + Rng.Rows.Count - 1
fCol = Rng.Column: eCol = fCol + Rng.Columns.Count - 1
iR = Target.Row: jC = Target.Column
For j = fCol To eCol
If j <> jC Then
tmp = cells(iR, j).Value 'Phong_Loai
If InStr(1, tmp, ".") > 0 Then
If phong = CLng(Split(tmp, ".")(0)) Then
MsgBox ("Trung phong Mon: " & cells(3, j))
Target.Value = Empty '***
Call TangToc(True)
Exit Sub
End If
End If
End If
Next j
NV = Range("A" & iR).Value
k = 0
For i = fRow To eRow
If i <> iR Then
If InStr(1, cells(i, jC), phong & ".") = 1 Then
If Phong_Loai = cells(i, jC) Then
MsgBox (Phong_Loai & " Nhap trung 2 lan")
Target.Value = Empty '***
Call TangToc(True)
Exit Sub
End If
k = k + 1
If k > 1 Then
MsgBox ("Ma Phong: " & phong & "co hon 2 giam thi")
Target.Value = Empty '***
Call TangToc(True)
Exit Sub
End If
ik = i
End If
End If
Next i
If k = 1 Then
For j = fCol To eCol
tmp = cells(ik, j) 'Phong_Loai
If InStr(1, tmp, ".") Then
If j <> jC Then
tmp = CLng(Split(tmp, ".")(0)) 'Phong
For i = fRow To eRow
If InStr(1, cells(i, j), ".") Then
If tmp = CLng(Split(cells(i, j), ".")(0)) Then
If NV = cells(i, 1) Then
MsgBox ("Trung Giam Thi: " & cells(ik, 8).Value)
Target.Value = Empty '***
Call TangToc(True)
Exit Sub
End If
End If
End If
Next i
End If
End If
Next j
End If
If Target.Value <> Phong_Loai Then Target.Value = Phong_Loai
If iR < eRow Then cells(iR + 1, jC).Select Else cells(fRow, jC + 1).Select
ElseIf Not Intersect(Target, Range(rGiamSat)) Is Nothing Then
Set Rng = Range(rGiamSat)
Phong_Loai = UCase(Target.Value)
If Phong_Loai = Empty Then Exit Sub
bTest = False
If InStr(1, Phong_Loai, "GS") > 0 Then
If Len(Phong_Loai) > 2 Then
tmp = Replace(Phong_Loai, "GS", "") 'STT Khu Vuc Giam Sat
If IsNumeric(tmp) Then
phong = CLng(tmp) 'STT Khu Vuc Giam Sat
If phong <= SoPhieuGS Then
Phong_Loai = "GS" & phong 'Khu Vuc Giam Sat
bTest = True
End If
End If
End If
End If
Call TangToc(False)
Target.Select
If bTest = False Then
MsgBox ("Nhap sai Phieu Giam Sat")
Target.Value = Empty '***
Call TangToc(True)
Exit Sub
End If
fRow = Rng.Row: eRow = fRow + Rng.Rows.Count - 1
fCol = Rng.Column: eCol = fCol + Rng.Columns.Count - 1
iR = Target.Row: jC = Target.Column
For j = fCol To eCol
If j <> jC Then
If Phong_Loai = cells(iR, j).Value Then
MsgBox ("Trung phong Mon: " & cells(3, j))
Target.Value = Empty '***
Call TangToc(True)
Exit Sub
End If
End If
Next j
For i = fRow To eRow
If i <> iR Then
If Phong_Loai = cells(i, jC) Then
MsgBox (Phong_Loai & ": Nhap trung GS " & cells(i, 8))
Target.Value = Empty '***
Call TangToc(True)
Exit Sub
End If
End If
Next i
If Target.Value <> Phong_Loai Then Target.Value = Phong_Loai
If iR < eRow Then cells(iR + 1, jC).Select Else cells(fRow, jC + 1).Select
End If
Call TangToc(True)
End Sub
Private Sub TangToc(ByVal bTest As Boolean)
Application.ScreenUpdating = bTest
Application.EnableEvents = bTest
End Sub
