Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([P5], Target) Is Nothing And Target.Count = 1 Then
Application.EnableEvents = False
[P9:AA9].ClearContents
[P9].FormulaR1C1 = "=INDEX(Tableau1[Nom Prénom],MATCH(R5C16,Tableau1[Dos],0))"
[U9].FormulaR1C1 = "=INDEX(Tableau1[Série],MATCH(R5C16,Tableau1[Dos],0))"
[V9].FormulaR1C1 = "=INDEX(Tableau1[A1],MATCH(R5C16,Tableau1[Dos],0))"
[W9].FormulaR1C1 = "=INDEX(Tableau1[B1],MATCH(R5C16,Tableau1[Dos],0))"
[X9].FormulaR1C1 = "=INDEX(Tableau1[A2],MATCH(R5C16,Tableau1[Dos],0))"
[Y9].FormulaR1C1 = "=INDEX(Tableau1[B2],MATCH(R5C16,Tableau1[Dos],0))"
[Z9].FormulaR1C1 = "=INDEX(Tableau1[Barr 1],MATCH(R5C16,Tableau1[Dos],0))"
[AA9].FormulaR1C1 = "=INDEX(Tableau1[Barr 2],MATCH(R5C16,Tableau1[Dos],0))"
[P9:AA9].Value = [P9:AA9].Value
Application.EnableEvents = True
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$P$5:$T$5" Then
Set MonDico = CreateObject("Scripting.Dictionary")
a = [Tableau1[Dos]]
For Each c In a
MonDico(UCase(c)) = UCase(c)
Next c
b = MonDico.keys
Call tri(b, LBound(b), UBound(b))
For Each c In b: temp = temp & c & ",": Next c
Target.Validation.Delete
Target.Validation.Add xlValidateList, Formula1:=Left(temp, Len(temp) - 1)
End If
End Sub
Sub tri(a, gauc, droi) ' Quick sort
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
Do While a(g) < ref: g = g + 1: Loop
Do While ref < a(d): d = d - 1: Loop
If g <= d Then
temp = a(g): a(g) = a(d): a(d) = temp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub
