1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| Option Explicit
Dim MonDico As New Scripting.Dictionary
Dim c As String
Dim temp As Variant
'----------------------------------------
Private Sub CbtAnnul_Click()
Unload Me
End Sub
Private Sub UsfTbAgt_Initialize()
Set MonDico = CreateObject("Scripting.Dictionary")
For Each c In Range("TbAg[Dpt]").Rows ' on explore la colonne de niveau 1
MonDico(c.Value) = "" ' on ajoute l'élément de la CbxDpt au dictionnaire
Next c
temp = MonDico.keys 'les clés du dico dans la table temp()
Call Tri(temp, LBound(temp), UBound(temp))
Me.CbxDpt.List = temp
End Sub
'----------------------------------------------
Private Sub CbxDpt_click()
Me.CbxPren.Clear 'CbxDpt = 1ère Combo CbxPren = 2ème combo
Set MonDico = CreateObject("Scripting.Dictionary")
For Each c In Range("TbAg[Dpt]").Rows ' on explore la colonne de niveau 1
If c = Me.CbxDpt Then MonDico(c.Offset(, -1).Value) = "" ' si CbxDpt alors on ajoute l'élément de la sous-famille au dictionnaire
Next c
temp = MonDico.keys 'les clés du dico dans la table temp()
Call Tri(temp, LBound(temp), UBound(temp))
Me.CbxPren.List = temp
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 |
Partager