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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
| Option Explicit
Public cbox As String 'définition de la variable cbox (combobox) comme variable fixe "public"
Public dl As Long 'définition de la variable dl (dernière ligne) comme variable entier long "public"
Private Sub bouton_fermeture_Click()
choix_periode.Hide 'fermeture de la fiche de choix de la période pour le listing
End Sub
Private Sub UserForm_Activate()
Application.ScreenUpdating = False 'mise à jour de l'écran bloquée
With Sheets("Base")
dl = .Range("A" & .Rows.Count).End(xlUp).Row 'définition de la valeur de la dernière ligne (variable dl)
cbox = "box_annee"
creer_liste_annee_sansdoublons .Range("A4:A" & dl) 'définition de la plage concernant les demandeurs
cbox = "box_mois"
creer_liste_mois_sansdoublons .Range("A4:A" & dl) 'définition de la plage concernant les demandeurs
End With
End Sub
Sub creer_liste_annee_sansdoublons(Plage As Range)
Dim Cell As Range, cible
Dim Un As Collection
Dim ssdoublon(), valeur As Long, i As Long
Set Un = New Collection
On Error Resume Next
For Each Cell In Plage
If Cell <> "" Then Un.Add Year(Cell), CStr(Year(Cell))
Next Cell
On Error GoTo 0
For i = 1 To Un.Count
ReDim Preserve ssdoublon(i - 1)
ssdoublon(i - 1) = Un.Item(i)
Next i
Do 'définition du système de tri croissant
valeur = 0
For i = UBound(ssdoublon) - 1 To 0 Step -1
If ssdoublon(i) > ssdoublon(i + 1) Then
cible = ssdoublon(i)
ssdoublon(i) = ssdoublon(i + 1)
ssdoublon(i + 1) = cible
valeur = 1
End If
Next i
Loop While valeur = 1
Me.Controls(cbox).List = ssdoublon()
Set Un = Nothing
End Sub
Sub creer_liste_mois_sansdoublons(Plage As Range)
Dim Cell As Range, cible
Dim Un As Collection
Dim ssdoublon(), valeur As Long, i As Long
Set Un = New Collection
On Error Resume Next
For Each Cell In Plage
If Cell <> "" Then Un.Add Month(Cell), CStr(Month(Cell))
Next Cell
On Error GoTo 0
For i = 1 To Un.Count
ReDim Preserve ssdoublon(i - 1)
ssdoublon(i - 1) = Un.Item(i)
Next i
Do 'définition du système de tri croissant
valeur = 0
For i = UBound(ssdoublon) - 1 To 0 Step -1
If ssdoublon(i) > ssdoublon(i + 1) Then
cible = ssdoublon(i)
ssdoublon(i) = ssdoublon(i + 1)
ssdoublon(i + 1) = cible
valeur = 1
End If
Next i
Loop While valeur = 1
Me.Controls(cbox).List = ssdoublon()
Set Un = Nothing
End Sub |
Partager