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
| Sub essai()
Dim DerLg As Range, Tbl, TblTri, Schoix As String, x As Integer
With Sheets("Feuil1")
Set DerLg = .Cells(.Rows.Count, Col).End(xlUp)
Tbl = .Range(.Cells(3, Col), DerLg)
Application.EnableEvents = False
.Range(.Cells(3, Col), DerLg).Sort key1:=.Cells(3, Col), order1:=xlAscending, Header:=xlNo
.Range(.Cells(3, Col), DerLg).RemoveDuplicates Columns:=1, Header:=xlNo
Set DerLg = .Cells(.Rows.Count, Col).End(xlUp)
TblTri = .Range(.Cells(3, Col), DerLg)
.Cells(3, Col).Resize(UBound(Tbl)) = Tbl
Set DerLg = .Cells(.Rows.Count, Col).End(xlUp)
.Cells(3, Col).Resize(UBound(Tbl)).Name = "CHOIX" & Col
Application.EnableEvents = True
Schoix = ""
Schoix = TblTri(1, 1)
For x = 2 To UBound(TblTri)
Schoix = Schoix & "," & TblTri(x, 1)
Next x
With .Range("CHOIX" & Col).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Schoix
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = False
End With
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then Col = "B": essai
If Target.Column = 3 Then Col = "C": essai
End Sub |
Partager