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
| Option Explicit
Dim periodeJour As Single, heureVerification As Single, tempsAlerteJour As Single
Dim nbLignes As Integer, iL As Integer
Dim rD As Range, rS As Range
Dim f As Worksheet
Sub ChienDeGarde()
'arret éventuel de la vérification
If ThisWorkbook.Names("OnOff").RefersToRange.Value = "Off" Then
If ThisWorkbook.Names("HeureVerification").RefersToRange.Value <> "" Then
On Error Resume Next
Application.OnTime _
ThisWorkbook.Names("HeureVerification").RefersToRange.Value, _
"ChienDeGarde", , False
On Error GoTo 0
End If
ThisWorkbook.Names("HeureVerification").RefersToRange.ClearContents
GoTo finChienDeGarde
End If
'vérifie la feuille
Verifier
'relance la garde
periodeJour = ThisWorkbook.Names("periode").RefersToRange.Value / 84600 '1 jour= 84600 secondes
heureVerification = Time + periodeJour
ThisWorkbook.Names("HeureVerification").RefersToRange.Value = heureVerification
Application.OnTime heureVerification, "ChienDeGarde", , True
finChienDeGarde:
End Sub
Sub Verifier()
'feuille à vérifier
Set rD = ThisWorkbook.Names("heureDepart").RefersToRange
Set rS = ThisWorkbook.Names("heureSonne").RefersToRange
Set f = rD.Parent
tempsAlerteJour = ThisWorkbook.Names("tempsAlerte").RefersToRange.Value / 1440 '1 jour=1440 minutes
nbLignes = f.Cells(f.Rows.Count, ThisWorkbook.Names("heureDepart").RefersToRange.Column).End(xlUp).Row _
- ThisWorkbook.Names("heureDepart").RefersToRange.Row
For iL = 1 To nbLignes
'si on a rempli l'heure de départ
If rD.Offset(iL, 0).Value <> "" Then
'si on n'a pas rempli l'heure sonné
If rS.Offset(iL, 0).Value = "" Then
's'il s'est passé plus de temps que le délai d'alerte
If Time > rD.Offset(iL, 0).Value + tempsAlerteJour Then
MsgBox "Ligne " & rD.Offset(iL, 0).Row & " non remplie!"
End If
End If
End If
Next iL
End Sub |
Partager