Erreur: Me.Controls("TextBox" & x).Value = Sheets("Base").Range("C" & i + 2).Offset(

jf-getaz

XLDnaute Nouveau
Bonjour à tous et bonne année,
je vous transmets une macro que je suis en train de faire (transformation d'un fichier) et j'ai une erreur, mais je ne trouve pas la sollution. L'erreur se trouve dans la listebox1 Pouvez-vous m'aider.

Merci

Private Sub CommandButton2_Click()
Dim i As Long

If TextBox1 = "" Then
If TextBox2 = "" Then
MsgBox "il faut au moins un nom ou un prénom SVP!!", vbCritical
Exit Sub
End If
End If


'Augmenter la vitesse d'éxécution, arreter les alertes
With Application
.Calculation = xlCalculationManual
.DisplayAlerts = False
.ScreenUpdating = False
End With
'Si une entrée du textbox est selectionner, sauvegarder sur le nom selectionner
For i = 1 To ListBox1.ListCount
If ListBox1.Selected(i) = True Then
With Worksheets("Base").Range("C" & i + 1)
If TextBox1.Value = "" Then .Offset(1, 0).Value = TextBox2.Value Else .Offset(1, 0).Value = TextBox1.Value
.Offset(1, 1).Value = TextBox2.Value
.Offset(1, 1).Value = TextBox2.Value
.Offset(1, 2).Value = TextBox3.Value
.Offset(1, 3).Value = TextBox4.Value
.Offset(1, 5).Value = TextBox5.Value
.Offset(1, 6).Value = TextBox6.Value
.Offset(1, 13).Value = TextBox7.Value
.Offset(1, 14).Value = TextBox8.Value
.Offset(1, 21).Value = TextBox9.Value
.Offset(1, 22).Value = TextBox10.Value
.Offset(1, 29).Value = TextBox11.Value
.Offset(1, 30).Value = TextBox12.Value
.Offset(1, 37).Value = TextBox13.Value
.Offset(1, 38).Value = TextBox14.Value
.Offset(1, 45).Value = TextBox15.Value
.Offset(1, 46).Value = TextBox16.Value
.Offset(1, 53).Value = TextBox17.Value
.Offset(1, 54).Value = TextBox18.Value
.Offset(1, 61).Value = TextBox19.Value
.Offset(1, 62).Value = TextBox20.Value
.Offset(1, 69).Value = TextBox21.Value
.Offset(1, 70).Value = TextBox22.Value
.Offset(1, 77).Value = TextBox23.Value
.Offset(1, 78).Value = TextBox24.Value
.Offset(1, 7).Value = TextBox25.Value
.Offset(1, 8).Value = TextBox26.Value
.Offset(1, 9).Value = TextBox27.Value
.Offset(1, 10).Value = TextBox28.Value
.Offset(1, 11).Value = TextBox29.Value
.Offset(1, 12).Value = TextBox40.Value
.Offset(1, 15).Value = TextBox41.Value
.Offset(1, 16).Value = TextBox42.Value
.Offset(1, 17).Value = TextBox43.Value
.Offset(1, 18).Value = TextBox44.Value
.Offset(1, 19).Value = TextBox45.Value
.Offset(1, 20).Value = TextBox46.Value
.Offset(1, 23).Value = TextBox47.Value
.Offset(1, 24).Value = TextBox48.Value
.Offset(1, 25).Value = TextBox49.Value
.Offset(1, 26).Value = TextBox40.Value
.Offset(1, 27).Value = TextBox41.Value
.Offset(1, 28).Value = TextBox42.Value
.Offset(1, 31).Value = TextBox43.Value
.Offset(1, 32).Value = TextBox44.Value
.Offset(1, 33).Value = TextBox45.Value
.Offset(1, 34).Value = TextBox46.Value
.Offset(1, 35).Value = TextBox47.Value
.Offset(1, 36).Value = TextBox48.Value
.Offset(1, 39).Value = TextBox49.Value
.Offset(1, 40).Value = TextBox50.Value
.Offset(1, 41).Value = TextBox51.Value
.Offset(1, 42).Value = TextBox52.Value
.Offset(1, 43).Value = TextBox53.Value
.Offset(1, 44).Value = TextBox54.Value
.Offset(1, 47).Value = TextBox55.Value
.Offset(1, 48).Value = TextBox56.Value
.Offset(1, 49).Value = TextBox57.Value
.Offset(1, 50).Value = TextBox58.Value
.Offset(1, 51).Value = TextBox59.Value
.Offset(1, 52).Value = TextBox60.Value
.Offset(1, 55).Value = TextBox61.Value
.Offset(1, 56).Value = TextBox62.Value
.Offset(1, 57).Value = TextBox63.Value
.Offset(1, 58).Value = TextBox64.Value
.Offset(1, 59).Value = TextBox65.Value
.Offset(1, 60).Value = TextBox66.Value
.Offset(1, 63).Value = TextBox67.Value
.Offset(1, 64).Value = TextBox68.Value
.Offset(1, 65).Value = TextBox69.Value
.Offset(1, 66).Value = TextBox70.Value
.Offset(1, 67).Value = TextBox71.Value
.Offset(1, 68).Value = TextBox72.Value
.Offset(1, 71).Value = TextBox73.Value
.Offset(1, 72).Value = TextBox74.Value
.Offset(1, 73).Value = TextBox75.Value
.Offset(1, 74).Value = TextBox76.Value
.Offset(1, 75).Value = TextBox77.Value
.Offset(1, 76).Value = TextBox78.Value
.Offset(1, 79).Value = TextBox79.Value
.Offset(1, 80).Value = TextBox80.Value
.Offset(1, 81).Value = TextBox81.Value
.Offset(1, 82).Value = TextBox82.Value
.Offset(1, 83).Value = TextBox83.Value
.Offset(1, 84).Value = TextBox84.Value
.Offset(1, 4).Value = TextBox85.Value

End With
VideTextbox
End If

Next i
With Worksheets("Base").Range("C3").End(xlDown)
If TextBox1.Value = "" Then .Offset(1, 0).Value = TextBox2.Value Else .Offset(1, 0).Value = TextBox1.Value
.Offset(1, 1).Value = TextBox2.Value
.Offset(1, 2).Value = TextBox3.Value
.Offset(1, 3).Value = TextBox4.Value
.Offset(1, 5).Value = TextBox5.Value
.Offset(1, 6).Value = TextBox6.Value
.Offset(1, 13).Value = TextBox7.Value
.Offset(1, 14).Value = TextBox8.Value
.Offset(1, 21).Value = TextBox9.Value
.Offset(1, 22).Value = TextBox10.Value
.Offset(1, 29).Value = TextBox11.Value
.Offset(1, 30).Value = TextBox12.Value
.Offset(1, 37).Value = TextBox13.Value
.Offset(1, 38).Value = TextBox14.Value
.Offset(1, 45).Value = TextBox15.Value
.Offset(1, 46).Value = TextBox16.Value
.Offset(1, 53).Value = TextBox17.Value
.Offset(1, 54).Value = TextBox18.Value
.Offset(1, 61).Value = TextBox19.Value
.Offset(1, 62).Value = TextBox20.Value
.Offset(1, 69).Value = TextBox21.Value
.Offset(1, 70).Value = TextBox22.Value
.Offset(1, 77).Value = TextBox23.Value
.Offset(1, 78).Value = TextBox24.Value
.Offset(1, 7).Value = TextBox25.Value
.Offset(1, 8).Value = TextBox26.Value
.Offset(1, 9).Value = TextBox27.Value
.Offset(1, 10).Value = TextBox28.Value
.Offset(1, 11).Value = TextBox29.Value
.Offset(1, 12).Value = TextBox40.Value
.Offset(1, 15).Value = TextBox41.Value
.Offset(1, 16).Value = TextBox42.Value
.Offset(1, 17).Value = TextBox43.Value
.Offset(1, 18).Value = TextBox44.Value
.Offset(1, 19).Value = TextBox45.Value
.Offset(1, 20).Value = TextBox46.Value
.Offset(1, 23).Value = TextBox47.Value
.Offset(1, 24).Value = TextBox48.Value
.Offset(1, 25).Value = TextBox49.Value
.Offset(1, 26).Value = TextBox40.Value
.Offset(1, 27).Value = TextBox41.Value
.Offset(1, 28).Value = TextBox42.Value
.Offset(1, 31).Value = TextBox43.Value
.Offset(1, 32).Value = TextBox44.Value
.Offset(1, 33).Value = TextBox45.Value
.Offset(1, 34).Value = TextBox46.Value
.Offset(1, 35).Value = TextBox47.Value
.Offset(1, 36).Value = TextBox48.Value
.Offset(1, 39).Value = TextBox49.Value
.Offset(1, 40).Value = TextBox50.Value
.Offset(1, 41).Value = TextBox51.Value
.Offset(1, 42).Value = TextBox52.Value
.Offset(1, 43).Value = TextBox53.Value
.Offset(1, 44).Value = TextBox54.Value
.Offset(1, 47).Value = TextBox55.Value
.Offset(1, 48).Value = TextBox56.Value
.Offset(1, 49).Value = TextBox57.Value
.Offset(1, 50).Value = TextBox58.Value
.Offset(1, 51).Value = TextBox59.Value
.Offset(1, 52).Value = TextBox60.Value
.Offset(1, 55).Value = TextBox61.Value
.Offset(1, 56).Value = TextBox62.Value
.Offset(1, 57).Value = TextBox63.Value
.Offset(1, 58).Value = TextBox64.Value
.Offset(1, 59).Value = TextBox65.Value
.Offset(1, 60).Value = TextBox66.Value
.Offset(1, 63).Value = TextBox67.Value
.Offset(1, 64).Value = TextBox68.Value
.Offset(1, 65).Value = TextBox69.Value
.Offset(1, 66).Value = TextBox70.Value
.Offset(1, 67).Value = TextBox71.Value
.Offset(1, 68).Value = TextBox72.Value
.Offset(1, 71).Value = TextBox73.Value
.Offset(1, 72).Value = TextBox74.Value
.Offset(1, 73).Value = TextBox75.Value
.Offset(1, 74).Value = TextBox76.Value
.Offset(1, 75).Value = TextBox77.Value
.Offset(1, 76).Value = TextBox78.Value
.Offset(1, 79).Value = TextBox79.Value
.Offset(1, 80).Value = TextBox80.Value
.Offset(1, 81).Value = TextBox81.Value
.Offset(1, 82).Value = TextBox82.Value
.Offset(1, 83).Value = TextBox83.Value
.Offset(1, 84).Value = TextBox84.Value
.Offset(1, 4).Value = TextBox85.Value

End With


ActiveWorkbook.Worksheets("Base").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Base").Sort.SortFields.Add Key:=Range("C3:C2072"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Base").Sort
.SetRange Range("A3:W2072")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


ActiveWorkbook.Save

With Application
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.ScreenUpdating = True
End With

initlistbox
VideTextbox

End Sub

Private Sub CommandButton29_Click()
ActiveWorkbook.Save
End Sub

Private Sub CommandButton3_Click()
Unload UserForm1
End Sub

Private Sub CommandButton4_Click()
Worksheets("Aide").Select
UserForm1.Hide
End Sub

Private Sub CommandButton5_Click()
initlistbox
VideTextbox
End Sub

Private Sub ListBox1_Change()
Dim i As Long
Dim x As Byte

With ListBox1
If .ListIndex = -1 Then Exit Sub
For i = 1 To ListBox1.ListCount
If .ListCount <= 2 Then Exit Sub
If .Selected(i) = True Then

TextBox1.Value = Sheets("Base").Range("C" & i + 2).Offset(0, 0).Value
TextBox2.Value = Sheets("Base").Range("C" & i + 2).Offset(0, 1).Value
TextBox3.Value = Sheets("Base").Range("C" & i + 2).Offset(0, 2).Value
TextBox4.Value = Sheets("Base").Range("C" & i + 2).Offset(0, 3).Value

For x = 5 To 85
Me.Controls("TextBox" & x).Value = Sheets("Base").Range("C" & i + 2).Offset(0, x).Value
Next x
.Selected(i) = True
End If

Next i
End With

End Sub

Private Sub Supprimer_Click()
Dim i As Long

Application.ScreenUpdating = False

With ListBox1
If .ListIndex = -1 Then Exit Sub
For i = 1 To ListBox1.ListCount
If .ListCount <= 2 Then Exit Sub

If .Selected(i) = True Then
Sheets("Base").Cells(i + 2, 1).EntireRow.Delete
.Selected(i) = False
End If
Next i
End With

Application.ScreenUpdating = True
initlistbox
VideTextbox
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If TextBox1 = "" Then Exit Sub
If IsNumeric(TextBox1.Text) Then
MsgBox "Pas de chiffre pour le nom SVP...ou alors avec des lettres...numéro éffacer!!!", vbCritical, "ATTENTION !"
TextBox1.Value = ""
Exit Sub
End If
mystring = TextBox2.Text

Me.TextBox1 = Format(TextBox1, "")
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox2 = "" Then Exit Sub
mystring = TextBox2.Text

Me.TextBox2 = Format(TextBox2, "")
End Sub

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox3 = "" Then Exit Sub
mystring = TextBox3.Text

Me.TextBox3 = Format(TextBox3, "")
End Sub

Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox4 = "" Then Exit Sub
mystring = TextBox4.Text

Me.TextBox4 = Format(TextBox4, "")
End Sub

Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox5 = "" Then Exit Sub
mystring = TextBox5.Text

Me.TextBox5 = Format(TextBox5, "")
End Sub

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox6 = "" Then Exit Sub

Me.TextBox6 = Format(TextBox6, "##"".""##"".""####")

If Not IsDate(TextBox6.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox6.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox7 = "" Then Exit Sub
mystring = TextBox7.Text

Me.TextBox7 = Format(TextBox7, "")
End Sub

Private Sub TextBox8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox8 = "" Then Exit Sub

Me.TextBox8 = Format(TextBox8, "##"".""##"".""####")

If Not IsDate(TextBox8.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox8.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox9 = "" Then Exit Sub
mystring = TextBox9.Text

Me.TextBox9 = Format(TextBox9, "")
End Sub

Private Sub TextBox10_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox10 = "" Then Exit Sub

Me.TextBox10 = Format(TextBox10, "##"".""##"".""####")

If Not IsDate(TextBox10.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox10.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox11_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox11 = "" Then Exit Sub
mystring = TextBox11.Text

Me.TextBox11 = Format(TextBox11, "")
End Sub

Private Sub TextBox12_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox12 = "" Then Exit Sub

Me.TextBox12 = Format(TextBox12, "##"".""##"".""####")

If Not IsDate(TextBox12.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox12.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox13_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox13 = "" Then Exit Sub
mystring = TextBox13.Text

Me.TextBox13 = Format(TextBox13, "")
End Sub

Private Sub TextBox14_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox14 = "" Then Exit Sub

Me.TextBox14 = Format(TextBox14, "##"".""##"".""####")

If Not IsDate(TextBox14.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox14.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox15_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox15 = "" Then Exit Sub
mystring = TextBox15.Text

Me.TextBox15 = Format(TextBox15, "")
End Sub

Private Sub TextBox16_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox16 = "" Then Exit Sub

Me.TextBox16 = Format(TextBox16, "##"".""##"".""####")

If Not IsDate(TextBox16.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox16.Value = ""
Exit Sub
End If

End Sub
Private Sub TextBox17_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox17 = "" Then Exit Sub
mystring = TextBox17.Text

Me.TextBox17 = Format(TextBox17, "")
End Sub
Private Sub TextBox18_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox18 = "" Then Exit Sub

Me.TextBox18 = Format(TextBox18, "##"".""##"".""####")

If Not IsDate(TextBox18.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox18.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox19_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox19 = "" Then Exit Sub
mystring = TextBox19.Text

Me.TextBox19 = Format(TextBox19, "")
End Sub

Private Sub TextBox20_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox20 = "" Then Exit Sub

Me.TextBox20 = Format(TextBox20, "##"".""##"".""####")

If Not IsDate(TextBox20.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox20.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox21_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox21 = "" Then Exit Sub
mystring = TextBox21.Text

Me.TextBox21 = Format(TextBox21, "")
End Sub

Private Sub TextBox22_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox22 = "" Then Exit Sub

Me.TextBox22 = Format(TextBox22, "##"".""##"".""####")

If Not IsDate(TextBox22.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox22.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox23_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox23 = "" Then Exit Sub
mystring = TextBox23.Text

Me.TextBox23 = Format(TextBox23, "")
End Sub

Private Sub TextBox24_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox24 = "" Then Exit Sub

Me.TextBox24 = Format(TextBox24, "##"".""##"".""####")

If Not IsDate(TextBox24.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox24.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox25_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox25 = "" Then Exit Sub
mystring = TextBox25.Text

Me.TextBox25 = Format(TextBox25, "")
End Sub

Private Sub TextBox26_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox26 = "" Then Exit Sub
mystring = TextBox26.Text

Me.TextBox26 = Format(TextBox26, "")
End Sub

Private Sub TextBox27_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox27 = "" Then Exit Sub
mystring = TextBox27.Text

Me.TextBox27 = Format(TextBox27, "")
End Sub

Private Sub TextBox28_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox28 = "" Then Exit Sub
mystring = TextBox28.Text

Me.TextBox28 = Format(TextBox28, "")
End Sub

Private Sub TextBox29_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox29 = "" Then Exit Sub
mystring = TextBox29.Text

Me.TextBox29 = Format(TextBox29, "")
End Sub

Private Sub TextBox30_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox30 = "" Then Exit Sub

Me.TextBox30 = Format(TextBox30, "##"".""##"".""####")

If Not IsDate(TextBox30.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox30.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox31_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox31 = "" Then Exit Sub
mystring = TextBox31.Text

Me.TextBox31 = Format(TextBox31, "")
End Sub

Private Sub TextBox32_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox32 = "" Then Exit Sub
mystring = TextBox32.Text

Me.TextBox32 = Format(TextBox32, "")
End Sub

Private Sub TextBox33_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox33 = "" Then Exit Sub
mystring = TextBox33.Text

Me.TextBox33 = Format(TextBox33, "")
End Sub

Private Sub TextBox34_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox34 = "" Then Exit Sub
mystring = TextBox34.Text

Me.TextBox34 = Format(TextBox34, "")
End Sub

Private Sub TextBox35_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox35 = "" Then Exit Sub
mystring = TextBox35.Text

Me.TextBox35 = Format(TextBox35, "")
End Sub

Private Sub TextBox36_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox36 = "" Then Exit Sub

Me.TextBox36 = Format(TextBox36, "##"".""##"".""####")

If Not IsDate(TextBox36.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox36.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox37_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox37 = "" Then Exit Sub
mystring = TextBox37.Text

Me.TextBox37 = Format(TextBox37, "")
End Sub

Private Sub TextBox38_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox38 = "" Then Exit Sub
mystring = TextBox38.Text

Me.TextBox38 = Format(TextBox38, "")
End Sub

Private Sub TextBox39_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox39 = "" Then Exit Sub
mystring = TextBox39.Text

Me.TextBox39 = Format(TextBox39, "")
End Sub

Private Sub TextBox40_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox40 = "" Then Exit Sub
mystring = TextBox40.Text

Me.TextBox40 = Format(TextBox40, "")
End Sub

Private Sub TextBox41_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox41 = "" Then Exit Sub
mystring = TextBox41.Text

Me.TextBox41 = Format(TextBox41, "")
End Sub

Private Sub TextBox42_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox42 = "" Then Exit Sub

Me.TextBox42 = Format(TextBox42, "##"".""##"".""####")

If Not IsDate(TextBox42.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox42.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox43_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox43 = "" Then Exit Sub
mystring = TextBox43.Text

Me.TextBox43 = Format(TextBox43, "")
End Sub

Private Sub TextBox44_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox44 = "" Then Exit Sub
mystring = TextBox44.Text

Me.TextBox44 = Format(TextBox44, "")
End Sub

Private Sub TextBox45_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox45 = "" Then Exit Sub
mystring = TextBox45.Text

Me.TextBox45 = Format(TextBox45, "")
End Sub

Private Sub TextBox46_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox46 = "" Then Exit Sub
mystring = TextBox46.Text

Me.TextBox46 = Format(TextBox46, "")
End Sub

Private Sub TextBox47_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox47 = "" Then Exit Sub
mystring = TextBox47.Text

Me.TextBox47 = Format(TextBox47, "")
End Sub

Private Sub TextBox48_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox48 = "" Then Exit Sub

Me.TextBox48 = Format(TextBox48, "##"".""##"".""####")

If Not IsDate(TextBox48.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox48.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox49_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox49 = "" Then Exit Sub
mystring = TextBox49.Text

Me.TextBox49 = Format(TextBox49, "")
End Sub

Private Sub TextBox50_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox50 = "" Then Exit Sub
mystring = TextBox50.Text

Me.TextBox50 = Format(TextBox50, "")
End Sub

Private Sub TextBox51_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox51 = "" Then Exit Sub
mystring = TextBox51.Text

Me.TextBox51 = Format(TextBox51, "")
End Sub
Private Sub TextBox52_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox52 = "" Then Exit Sub
mystring = TextBox52.Text

Me.TextBox52 = Format(TextBox52, "")
End Sub

Private Sub TextBox53_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox53 = "" Then Exit Sub
mystring = TextBox53.Text

Me.TextBox53 = Format(TextBox53, "")
End Sub

Private Sub TextBox54_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox54 = "" Then Exit Sub

Me.TextBox54 = Format(TextBox54, "##"".""##"".""####")

If Not IsDate(TextBox54.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox54.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox55_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox55 = "" Then Exit Sub
mystring = TextBox55.Text

Me.TextBox55 = Format(TextBox55, "")
End Sub

Private Sub TextBox56_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox56 = "" Then Exit Sub
mystring = TextBox56.Text

Me.TextBox56 = Format(TextBox56, "")
End Sub

Private Sub TextBox57_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox57 = "" Then Exit Sub
mystring = TextBox57.Text

Me.TextBox57 = Format(TextBox57, "")
End Sub

Private Sub TextBox58_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox58 = "" Then Exit Sub
mystring = TextBox58.Text

Me.TextBox58 = Format(TextBox58, "")
End Sub

Private Sub TextBox59_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox59 = "" Then Exit Sub
mystring = TextBox59.Text

Me.TextBox59 = Format(TextBox59, "")
End Sub

Private Sub TextBox60_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox60 = "" Then Exit Sub

Me.TextBox60 = Format(TextBox60, "##"".""##"".""####")

If Not IsDate(TextBox60.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox60.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox61_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox61 = "" Then Exit Sub
mystring = TextBox61.Text

Me.TextBox61 = Format(TextBox61, "")
End Sub

Private Sub TextBox62_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox62 = "" Then Exit Sub
mystring = TextBox62.Text

Me.TextBox62 = Format(TextBox62, "")
End Sub

Private Sub TextBox63_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox63 = "" Then Exit Sub
mystring = TextBox63.Text

Me.TextBox63 = Format(TextBox63, "")
End Sub

Private Sub TextBox64_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox64 = "" Then Exit Sub
mystring = TextBox64.Text

Me.TextBox64 = Format(TextBox64, "")
End Sub

Private Sub TextBox65_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox65 = "" Then Exit Sub
mystring = TextBox65.Text

Me.TextBox65 = Format(TextBox65, "")
End Sub

Private Sub TextBox66_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox66 = "" Then Exit Sub

Me.TextBox66 = Format(TextBox60, "##"".""##"".""####")

If Not IsDate(TextBox66.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox66.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox67_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox67 = "" Then Exit Sub
mystring = TextBox67.Text

Me.TextBox67 = Format(TextBox67, "")
End Sub

Private Sub TextBox68_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox68 = "" Then Exit Sub
mystring = TextBox68.Text

Me.TextBox68 = Format(TextBox68, "")
End Sub

Private Sub TextBox69_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox69 = "" Then Exit Sub
mystring = TextBox69.Text

Me.TextBox69 = Format(TextBox69, "")
End Sub

Private Sub TextBox70_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox70 = "" Then Exit Sub
mystring = TextBox70.Text

Me.TextBox70 = Format(TextBox70, "")
End Sub

Private Sub TextBox71_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox71 = "" Then Exit Sub
mystring = TextBox71.Text

Me.TextBox71 = Format(TextBox71, "")
End Sub
Private Sub TextBox72_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox72 = "" Then Exit Sub

Me.TextBox72 = Format(TextBox72, "##"".""##"".""####")

If Not IsDate(TextBox72.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox72.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox73_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox73 = "" Then Exit Sub
mystring = TextBox73.Text

Me.TextBox73 = Format(TextBox73, "")
End Sub

Private Sub TextBox74_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox74 = "" Then Exit Sub
mystring = TextBox74.Text

Me.TextBox74 = Format(TextBox74, "")
End Sub

Private Sub TextBox75_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox75 = "" Then Exit Sub
mystring = TextBox75.Text

Me.TextBox75 = Format(TextBox75, "")
End Sub

Private Sub TextBox76_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox76 = "" Then Exit Sub
mystring = TextBox76.Text

Me.TextBox76 = Format(TextBox76, "")
End Sub

Private Sub TextBox77_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox77 = "" Then Exit Sub
mystring = TextBox77.Text

Me.TextBox77 = Format(TextBox77, "")
End Sub
Private Sub TextBox78_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox78 = "" Then Exit Sub

Me.TextBox78 = Format(TextBox78, "##"".""##"".""####")

If Not IsDate(TextBox78.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox78.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox79_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox79 = "" Then Exit Sub
mystring = TextBox79.Text

Me.TextBox79 = Format(TextBox79, "")
End Sub

Private Sub TextBox80_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox80 = "" Then Exit Sub
mystring = TextBox80.Text

Me.TextBox80 = Format(TextBox80, "")
End Sub

Private Sub TextBox81_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox81 = "" Then Exit Sub
mystring = TextBox81.Text

Me.TextBox81 = Format(TextBox81, "")
End Sub

Private Sub TextBox82_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox82 = "" Then Exit Sub
mystring = TextBox82.Text

Me.TextBox82 = Format(TextBox82, "")
End Sub

Private Sub TextBox83_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox83 = "" Then Exit Sub
mystring = TextBox83.Text

Me.TextBox83 = Format(TextBox83, "")
End Sub
Private Sub TextBox84_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox84 = "" Then Exit Sub

Me.TextBox84 = Format(TextBox84, "##"".""##"".""####")

If Not IsDate(TextBox84.Text) Then
MsgBox "Entrer une date valide SVP...date éffacer!!!", vbCritical, "ATTENTION !"
TextBox84.Value = ""
Exit Sub
End If

End Sub

Private Sub TextBox85_Change()
If TextBox85 = "" Then Exit Sub
mystring = TextBox85.Text

Me.TextBox85 = Format(TextBox85, "")
End Sub

Private Sub UserForm_Initialize()
initlistbox
End Sub

Private Sub initlistbox()
Dim c As Range
Dim x As Byte
Dim der As Long

Me.ListBox1.Clear

der = Sheets("Base").Range("C3").End(xlDown).Row
x = 0

For Each c In Sheets("Base").Range("C2:C" & der)
With ListBox1
.AddItem c
.List(x, 0) = c
.List(x, 1) = c.Offset(0, 1)
x = x + 1
End With
Next c

End Sub

Private Sub VideTextbox()
Dim objControl As Control

For Each objControl In UserForm1.Controls
If TypeOf objControl Is MSForms.TextBox Then
objControl.Text = ""
End If
Next
End Sub
 
Dernière édition:

Bebere

XLDnaute Barbatruc
Re : Erreur: Me.Controls("TextBox" & x).Value = Sheets("Base").Range("C" & i + 2).Of

bonjour
tu peux écrire
If TextBox1 = "" and TextBox2 = ""Then
MsgBox "il faut au moins un nom ou un prénom SVP!!", vbCritical
Exit Sub
End If




For i = 1 To ListBox1.ListCount
écrire
For i = 0 To ListBox1.ListCount-1

à bientôt
 

jf-getaz

XLDnaute Nouveau
Re : Erreur: Me.Controls("TextBox" & x).Value = Sheets("Base").Range("C" & i + 2).Of

Merci Bebere, mais cela n'a pas résolu mon problème, je l'ai momentanément contourné en reprenant la fonction copie pour chaque ligne, ex. TextBox4.Value = Sheets("Base").Range("C" & i + 2).Offset(0, 3).Value mais cela... le problème est que le fichier même compressé est trop grand pour être mis en ligne.

Merci et à bientôt
 

Pierrot93

XLDnaute Barbatruc
Re : Erreur: Me.Controls("TextBox" & x).Value = Sheets("Base").Range("C" & i + 2).Of

Bonjour,

rarement besoin du fichier original, simplement un tout petit fichier représentant uniquement le problème posé avec le résultat attendu.... Pas top d'analyser ainsi un code jeté sur un post.....

bonne journée
@+
 

Discussions similaires

Réponses
1
Affichages
206

Statistiques des forums

Discussions
311 733
Messages
2 082 019
Membres
101 872
dernier inscrit
Colin T