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
|
With bv 'affiche l'USF en bas à droite
.Top = 250
.Left = 250
End With
Label3.Font.Size = 8
Label3.ForeColor = RGB(0, 0, 255)
Label3.Font.Bold = True
Me.Label3.Caption = Sheets("macros").Range("c11").Value
Me.Label11.Caption = Sheets("macros").Range("a17")
Label6.Font.Size = 8
Label6.ForeColor = RGB(255, 0, 0)
Label6.Font.Bold = True
Label8.Font.Size = 8
Label8.ForeColor = RGB(0, 0, 255)
Label8.Font.Bold = True
Label11.Font.Size = 8
Label11.ForeColor = RGB(0, 0, 255)
Label11.Font.Bold = True
Label7.Font.Size = 8
Label7.ForeColor = RGB(255, 0, 0)
Label7.Font.Bold = True
Label16.Font.Size = 8
Label16.ForeColor = RGB(255, 0, 0)
Label16.Font.Bold = True
Dim cellule As Range
With Sheets("listetag")
On Error Resume Next
Set cellule = .Columns("A").Find(Me.TextBox1.Value, .Range("A65536"), xlValues)
If Not cellule Is Nothing Then
Dim b As Range
On Error Resume Next
Set b = Workbooks("adressesbi.xlsm").Sheets("contacts").Range("a1:a" & Workbooks("bv chilly-mazarin.xlsm").Sheets("contacts").Range("a65536").End(xlUp).Row).Find(cellule.Offset(0, 4).Value)
If Not b Is Nothing Then
Me.Label16.Caption = b.Offset(0, 9).Value
End If
End If
End With
Me.TextBox2.Value = Sheets("macros").Range("a138").Value
Me.TextBox3.Value = Sheets("macros").Range("a41").Value
Dim dat As String
Dim vieux As String
Dim vieux2 As String
Dim rng As Range, cell2 As Range
Set rng = Sheets("archivage").Range("a2:a65000")
Sheets("tri").Cells.Clear
Sheets("tri").Range("a:a").NumberFormat = "0"
Sheets("tri").Range("b:b").NumberFormat = "0"
Sheets("tri").Range("B1").FormulaR1C1 = "=MAX(C[-1])"
For Each cell2 In rng
If cell2.Value <> "" Then
If cell2.Value = Me.TextBox1.Value Then
If Format((cell2.Offset(0, 7).Value), "yyyy") = Sheets("macros").Range("a39").text Then
Sheets("tri").Range("A65536").End(xlUp).Offset(1, 0) = cell2.Offset(0, 6).Value
If cell2.Offset(0, 6).Value = Sheets("tri").Range("B1").Value Then
dat = cell2.Offset(0, 7).Value
vieux = cell2.Offset(0, 10).Value
vieux2 = cell2.Value & "-" & cell2.Offset(0, 6).Value & "-" & cell2.Offset(0, 7).Value & "-"
End If
End If
End If
End If
Next cell2
Dim madate As Date
madate = DateAdd("m", "-" & Sheets("macros").Range("a41").Value, Date)
On Error GoTo fin
If dat < madate Then
Label13.Font.Size = 8
Label13.ForeColor = RGB(255, 0, 0)
Label13.Font.Bold = True
Me.Label13.Caption = "Dernière Feuille reçue: " & vieux
Else
Me.Label13.Caption = ""
End If
If Me.Label13.Caption = "Dernière dépêche reçue: " Then Me.Label13.Caption = "Dernière Feuille reçue: " & vieux2
If ActiveCell.Offset(1, 0) = "" Then GoTo fin
Call CommandButton9_Click
Call CommandButton10_Click
fin::
Sheets("tri").Cells.Clear
Set rng = Nothing |