Option Explicit
Dim Dic As Object, sArr As Variant, khStr As String
Private Sub Worksheet_Activate()
Call Add_Dic
End Sub
Private Sub Worksheet_SelectionChange(ByVal vitri As Range)
Dim kh
If vitri.Address = "$L$5" Then
If Dic Is Nothing Then Call Add_Dic
kh = Range("L5").Value
If kh <> "" Then If Not Dic.exists(kh) Then Range("L5") = ""
Me.ComboBox2.List = sArr
Me.ComboBox2.Visible = True
Me.ComboBox2.Activate
Me.ComboBox2.Text = kh
khStr = kh
Else
If Me.ComboBox2.Visible = True Then Me.ComboBox2.Visible = False
End If
End Sub
Private Sub ComboBox2_Change() 'NHAP TEN KHACH HANG TRUC TIEP
On Error Resume Next
Dim kh As String, tmp
kh = UCase(Me.ComboBox2.Text)
If kh = "" Then
Me.ComboBox2.List = Dic.keys
Me.ComboBox2.TopIndex = 0
ElseIf Not Dic.exists(kh) Then
If Len(khStr) >= Len(kh) Then sArr = Dic.keys
khStr = kh
Call CreateArr
Me.ComboBox2.List = sArr
Me.ComboBox2.DropDown
Else
Range("L5").Value = Me.ComboBox2
End If
End Sub
Private Sub ComboBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean) 'DOUBLE CLICK CHON
Me.ComboBox2.DropDown
End Sub
Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 'PHIM ENTER CHON
If KeyCode = 40 Then
Me.ComboBox2.DropDown
End If
If KeyCode = 13 Then
If Not Dic.exists(UCase(Me.ComboBox2.Text)) Then Me.ComboBox2.Text = ""
Range("L25").Activate
End If
End Sub
Private Sub CreateArr()
Dim i As Long, sRow As Long, k As Long
sRow = UBound(sArr)
For i = 0 To sRow
If InStr(1, sArr(i), khStr) Then
sArr(k) = sArr(i)
k = k + 1
End If
Next i
If k > 0 Then
ReDim Preserve sArr(0 To k)
Else
sArr = Array("")
End If
End Sub
Private Sub Add_Dic()
Dim i As Long, sRow As Long, tmp(), ikey
Set Dic = CreateObject("scripting.dictionary")
With Sheet2
i = .Range("C1000000").End(xlUp).Row
If i < 26 Then
Dic.Add Empty, Empty
Else
tmp = .Range("C26:C" & i).Value
.Range("C26:C" & i).Sort .Range("C26"), xlAscending
sArr = .Range("C26:C" & i).Value
.Range("C26:C" & i).Value = tmp
sRow = UBound(sArr)
For i = 1 To sRow
ikey = UCase(sArr(i, 1))
If Len(ikey) > 0 Then Dic.Item(ikey) = Empty
Next i
End If
End With
Erase tmp
sArr = Dic.keys
With Range("L5")
Me.ComboBox2.Height = .Height + 5
Me.ComboBox2.Width = .Width + 5
Me.ComboBox2.Top = .Top
Me.ComboBox2.Left = .Left
Me.ComboBox2.Font.Size = 22
End With
End Sub