Private Sub CommandButton1_Click()
Dim I As Byte, Num As Long
Dim MesClients As Object
Dim tmp, temp
Set MesClients = CreateObject("Scripting.Dictionary")
With Sheets("Clients")
Num = .Range("A65535").End(xlUp).Row + 1
For I = 1 To 10
.Cells(Num, I).Value = Me.Controls("TextBox" & I).Value
Next I
For I = 2 To Num + 1
If Not MesClients.Exists(.Cells(I, 1).Value) Then _
MesClients.Add .Cells(I, 1).Value, .Cells(I, 1).Value
Next I
temp = Application.Transpose(MesClients.items)
For I = 1 To MesClients.Count
tmp = tmp & "," & temp(I, 1)
Next I
tmp = Right(tmp, Len(tmp) - 1)
End With
With [B9].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=tmp
End With
Unload Me
End Sub