Bonjour,
Un code qui fonctionne parfaitement bien en pas à pas avec F8 et même F5 une fois lancé plante systématiquement quand j'essaye de le lancer de façon "normal":


Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
 
Function estnombre(Ou As String) As Long
 
    For i = 1 To Len(Ou)
 
        If Mid(Ou, i, 1) Like "[0-9]" Then        
 
        estnombre = i - 5
 
        Exit Function
 
    End If
 
Next i
 
End Function
 
Function estValRech(Ou As String) As Long
 
    For i = 1 To Len(Ou)
 
        If Mid(Ou, i, 1) Like "[0-9]" Then        
 
        estValRech = i
 
        Exit Function
 
    End If
 
Next i
 
End Function
 
Function ZoneTrouv(ByVal INSEE As String, ByVal Feuil As Long) As Long
    derlig = Workbooks("CLM_CLD_CITIS XXXX.xlsm").Worksheets(Feuil).Range("A500000").End(xlUp).Row
 
    For i = 8 To derlig
 
        Range("D" & i).Select
 
        Debug.Print Format(Workbooks("CLM_CLD_CITIS XXXX.xlsm").Worksheets(Feuil).Range("D" & i), "[>=3000000000000]#"" ""##"" ""##"" ""##"" ""###"" ""###"" | ""##;#"" ""##"" ""##"" ""##"" ""###"" ""###")
        Debug.Print INSEE
 
        If Format(Workbooks("CLM_CLD_CITIS XXXX.xlsm").Worksheets(Feuil).Range("D" & i), "[>=3000000000000]#"" ""##"" ""##"" ""##"" ""###"" ""###"" | ""##;#"" ""##"" ""##"" ""##"" ""###"" ""###") = INSEE Then
 
            ZoneTrouv = i
 
            Application.EnableEvents = TRUE
 
            Exit Function
 
        End If
    Next i
End Function
 
Private Sub CommandButton1_Click()
    If UserForm3.CommandButton1 = FALSE Then
 
        EnvoiMail
 
    End If
 
End Sub
 
Public Sub ListBox1_Change() 
 
    If TEXTE = "X" Then
        TEXTE = ""
        Exit Sub
    End If
 
    Application.EnableEvents = FALSE
    Application.Calculation = xlCalculationManual
 
    UserForm4.Show 0
 
    UserForm4.Width = 700
    UserForm4.Height = 700
 
    UserForm4.TextBox1.Value = "SITUATION:"
    If ListBox1.ListIndex = -1 Then Exit Sub
 
    If UserForm3.ListBox1.List(ListBox1.ListIndex) <> "" Then
 
        Deb = InStr(1, UserForm3.ListBox1.List(ListBox1.ListIndex),        '") + 1
 
        NoINSEE = estValRech(UserForm3.ListBox1.List(ListBox1.ListIndex))       
        INSEE = Mid(UserForm3.ListBox1.List(ListBox1.ListIndex), NoINSEE, 23)
 
        Dim Ranj    As Range
 
        Ongl = 3
 
        Workbooks("CLM_CLD_CITIS XXXX.xlsm").Activate
        Worksheets(Ongl).Activate
        Range("D:D").Select
 
        LigOu = ZoneTrouv(INSEE, Ongl)   
 
        If LigOu = 0 Then
            Ongl = 2
            Workbooks("CLM_CLD_CITIS XXXX.xlsm").Activate
            Worksheets(Ongl).Activate
            Range("D:D").Select
            LigOu = ZoneTrouv(INSEE, Ongl)
        End If
 
        NbRept = Application.WorksheetFunction.CountIf(Workbooks("CLM_CLD_CITIS XXX.xlsm").Worksheets(Ongl).Range("B:B"), Workbooks("CLM_CLD_CITIS XXX.xlsm").Worksheets(Ongl).Cells(LigOu, 2))
 
        Ou = LigOu + (NbRept - 1)
 
        DerKol = Workbooks("CLM_CLD_CITIS XXXX.xlsm").Worksheets(Ongl).Cells(7, Application.Columns.Count).End(xlToLeft).Column
 
        For Z = 1 To DerKol
 
            UserForm4.TextBox1.Value = UserForm4.TextBox1.Value & Chr(10) & Workbooks("CLM_CLD_CITIS XXXX.xlsm").Worksheets(Ongl).Cells(Ou, Z)
            UserForm4.Repaint
 
        Next Z
 
        UserForm3.ListBox1.ListIndex = -1
 
    End If
 
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = TRUE
 
    Exit Sub
 
End Sub