Private Sub CommandButton1_Click()
Dim S As Worksheet
Dim R As Range
Dim var
Dim i&
Dim j&
Dim C As Control
Set S = Sheets("test")
Set R = S.Range("a1:a" & S.[a65536].End(xlUp).Row & "")
var = R
For i& = 1 To UBound(var, 1)
For j& = 1 To UBound(var, 2)
If Trim(LCase(var(i&, j&))) = Trim(LCase(TextBox1)) Then
MsgBox "Le nom " & var(i&, j&) & " existe déjà"
'--- efface les TextBox sauf la TextBox2 qui contient la date ---
For Each C In Me.Controls
If TypeName(C) = "TextBox" Then
If C.Name <> "TextBox2" Then C = ""
End If
Next C
'----------------------------------------------------------------
End If
Next j&
Next i&
''' votre traitement si le nom n'a pas été trouvé '''
MsgBox "votre traitement ..."
End Sub
Private Sub UserForm_Initialize()
TextBox2 = Format(Now, "dd/mm/yy")
TextBox3 = "Autre chose à effacer"
Label1.Caption = "Nom"
Label2.Caption = "Date"
Label3.Caption = "Autre chose"
CommandButton1.Caption = "Valider"
End Sub