I mean Validation list not listbox ..
This is the code I found but it removes the original list
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strVal As String
Dim strEntry As String
On Error Resume Next
strVal = Target.Validation.Formula1
If Not strVal = vbNullString Then
strEntry = Target
Application.EnableEvents = False
With Sheet1.Range("MyList")
.Replace What:=strEntry, Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.Range("A1", .Range("A65536").End(xlUp)).Name = "MyList"
End With
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
As an idea but I can't figure it out .. store my original list and deal with the stored array not with th origninal list!!
Bookmarks