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
| Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lst As String
Dim c As Range
If Target.Count = 1 And Target.Column = 1 And Target.Row > 1 Then
If Target.Value <> "" Then
Application.ScreenUpdating = False
Target.Offset(0, 1).Validation.Delete
With Sheets("Contacts")
.AutoFilterMode = False
.Range("Company").AutoFilter Field:=1, Criteria1:=Target.Value
For Each c In .Range("Contact").SpecialCells(xlCellTypeVisible)
If c.Row > 1 Then Lst = Lst & "," & c.Value
Next c
.AutoFilterMode = False
End With
Lst = Mid(Lst, 2)
If Lst <> "" Then Target.Offset(0, 1).Validation.Add Type:=xlValidateList, Formula1:=Lst
Else
Application.EnableEvents = False
Target.Offset(0, 1).ClearContents
Application.EnableEvents = True
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Kol As New Collection
Dim Lst As String
Dim i As Integer
Dim c As Range
If Target.Count = 1 And Target.Column = 1 And Target.Row > 1 Then
Target.Validation.Delete
With Sheets("Contacts")
For Each c In .Range("Company")
On Error Resume Next
Kol.Add c.Value, c.Value
On Error GoTo 0
Next c
End With
For i = 1 To Kol.Count
Lst = Lst & "," & Kol(i)
Next i
Lst = Mid(Lst, 2)
If Lst <> "" Then Target.Validation.Add Type:=xlValidateList, Formula1:=Lst
End If
End Sub |
Partager