Option Explicit
Dim dicDS As Object
Private Sub Worksheet_Change(ByVal Target As Range)
Dim maSP$, loai$, DungSai, DungSai_Y, iR&, jC&
If Target.Row < 5 Or Target.Column < 10 Or Target.Column > 11 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Len(Target.Value) = 0 Then Exit Sub
If dicDS Is Nothing Then Call Create_Dic
iR = Target.Row: jC = Target.Column
loai = Range("H" & iR).Value
If dicDS.exists(loai) = False Then Exit Sub
maSP = loai & "|" & Range("F" & iR).Value
DungSai = Target.Value
If dicDS.exists(maSP) = True Then
Application.EnableEvents = False
If jC = 10 Then
If dicDS.exists(maSP & "|" & DungSai) = False Then
MsgBox "Dung Sai Huong_X khong dung!" & Chr(10) & Chr(10) & _
"Nhap lai theo cac gia tri:" & dicDS.Item(maSP)
Target.Value = Empty: Target.Select
GoTo Thoat
End If
ElseIf jC = 11 Then
DungSai_Y = dicDS.Item(maSP & "|" & Range("J" & iR).Value)
If Len(Range("J" & iR).Value) = 0 Then
MsgBox "Phai nhap Dung Sai Huong_X truoc khi nhap Dung Sai Huong_Y! "
Target.Value = Empty: Target.Offset(, -1).Select
GoTo Thoat
ElseIf DungSai_Y <> DungSai Then
MsgBox "Dung sai Huong_Y khong dung!" & Chr(10) & Chr(10) & _
"Nhap lai theo gia tri: " & Format(DungSai_Y, "0.0##")
Target.Value = Empty
Target.Select
GoTo Thoat
End If
End If
Thoat:
Application.EnableEvents = True
End If
End Sub
Private Sub Worksheet_Activate()
Call Create_Dic
End Sub
Sub Create_Dic()
Dim sArr(), aLoai(), maSP$, sRow&, n&, i&
Set dicDS = CreateObject("scripting.dictionary")
aLoai = Range("M1:N" & Range("M" & Rows.Count).End(xlUp).Row).Value 'Loai 1, Loai 2
On Error Resume Next
For n = 1 To UBound(aLoai)
dicDS.Item(aLoai(n, 1)) = ""
With Sheets("LOAI" & Split(aLoai(n, 1), " ")(1))
If Err.Number <> 0 Then GoTo Thoat
sArr = .Range("B5", .Range("D" & Rows.Count).End(xlUp)).Value
End With
sRow = UBound(sArr)
For i = 1 To sRow
If sArr(i, 1) <> Empty Then
maSP = aLoai(n, 1) & "|" & sArr(i, 1)
dicDS.Item(maSP) = Chr(10) & sArr(i, 2) & Chr(10) & sArr(i + 1, 2) & Chr(10) & sArr(i + 2, 2)
End If
dicDS.Item(maSP & "|" & sArr(i, 2)) = sArr(i, 3)
Next i
Thoat:
Err = 0
Next n
End Sub
