Private Sub Workbook_Open()
With Sheets("Personnel").[A2].CurrentRegion
Message .Columns("D").Cells, "Visite médicale"
Message .Columns("F").Cells, "Permis"
Message .Columns("I").Cells, "Souscrit"
Message .Columns("J").Cells, "AFGSU 2"
End With
With Sheets("Véhicules").[A2].CurrentRegion
Message .Columns("E").Cells, "C.T. valide"
End With
End Sub
Sub Message(r As Range, titre$)
Dim a() As Date, n&, mes$
For Each r In r
If IsDate(r) Then If r >= Date And r <= Date + 30 Then ReDim Preserve a(n): a(n) = r: n = n + 1
Next r
If n Then
tri a, 0, n - 1 'classement des dates
mes = "Date" & IIf(n > 1, "s", "") & " : " & vbLf
For n = 0 To UBound(a)
mes = mes & vbLf & a(n)
Next n
MsgBox mes, , titre
Else
MsgBox "Aucun RV dans les 30 jours qui viennent", , titre
End If
End Sub
Sub tri(a, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
Do While a(g) < ref: g = g + 1: Loop
Do While ref < a(d): d = d - 1: Loop
If g <= d Then
temp = a(g): a(g) = a(d): a(d) = temp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub