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 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
| Option Explicit
'/!\ ACTIVER LA REFERENCE MICROSOFT SCRIPTING RUNTIME
Private Sub UserForm_Initialize()
Remplir Me.cdi, 1
Remplir Me.codepostal1, 2
Remplir Me.Choixcommune, 4
End Sub
Private Sub Remplir(ByVal LST As Object, ByVal Col As Integer, Optional ByVal Crit As String, Optional ByVal ColCrit As Integer)
Dim MonDico As New Scripting.Dictionary
Dim LastLig As Long, i As Long
Dim Ajout As Boolean
Dim Tmp As String
LST.Clear
With Worksheets("commune")
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To LastLig
Ajout = Crit = "" Or .Cells(i, Application.Max(1, ColCrit)) = Crit
If Ajout Then
Tmp = .Cells(i, Col).Value
If Not MonDico.Exists(Tmp) Then MonDico.add Tmp, Tmp
End If
Next i
End With
If MonDico.Count >= 0 Then LST.List = MonDico.Keys
Set MonDico = Nothing
End Sub
Private Sub Choixcommune_Change()
Dim Lig As Long
If Me.Choixcommune.ListIndex > -1 Then
Me.cdi.ListIndex = -1
Lig = LaLigne(Me.Choixcommune, 4)
Importer Lig
End If
End Sub
Private Sub cdi_Change()
Dim Lig As Long
If Me.cdi.ListIndex > -1 Then
Me.Choixcommune.ListIndex = -1
Lig = LaLigne(Me.cdi, 1)
Importer Lig
End If
End Sub
Private Sub codepostal1_Change()
Vider
If Me.codepostal1.ListIndex > -1 Then
Remplir Me.Choixcommune, 4, Me.codepostal1, 2
Remplir Me.cdi, 1, Me.codepostal1, 2
End If
End Sub
Private Sub Importer(ByVal Lig As Integer)
Vider
If Lig > 0 Then
With Worksheets("commune")
Me.insee.Value = .Range("A" & Lig)
Me.cdp.Value = .Range("B" & Lig)
Me.cable.Value = .Range("N" & Lig)
Me.piquetage.Value = .Range("O" & Lig)
Me.compactage.Value = .Range("P" & Lig)
Me.extension.Value = .Range("H" & Lig)
Me.branchement.Value = .Range("I" & Lig)
Me.liaisonb.Value = .Range("J" & Lig)
Me.identificationcable.Value = .Range("Q" & Lig)
Me.piquetagesout.Value = .Range("R" & Lig)
Me.compactagesout.Value = .Range("S" & Lig)
Me.centre.Value = .Range("C" & Lig)
Me.mail.Value = .Range("U" & Lig)
Me.moad.Value = .Range("V" & Lig)
Me.comm.Value = .Range("D" & Lig)
Me.moar.Value = .Range("E" & Lig)
Me.cpc.Value = .Range("X" & Lig)
Me.ccs.Value = .Range("Y" & Lig)
End With
End If
End Sub
Private Sub Vider()
Me.insee.Value = ""
Me.cdp.Value = ""
Me.cable.Value = ""
Me.piquetage.Value = ""
Me.compactage.Value = ""
Me.extension.Value = ""
Me.branchement.Value = ""
Me.liaisonb.Value = ""
Me.identificationcable.Value = ""
Me.piquetagesout.Value = ""
Me.compactagesout.Value = ""
Me.centre.Value = ""
Me.mail.Value = ""
Me.moad.Value = ""
Me.comm.Value = ""
Me.moar.Value = ""
Me.cpc.Value = ""
Me.ccs.Value = ""
End Sub
Private Function LaLigne(ByVal Tmp As String, ByVal Col As Integer) As Long
Dim c As Range
Set c = Worksheets("commune").Columns(Col).Find(Tmp, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
LaLigne = c.Row
Set c = Nothing
End If
End Function |
Partager