AW: Makro alle 5 Minuten ausführen
18.09.2010 08:56:45
Nepumuk
Hallo,
jetzt mal ein Beispiel:
' **********************************************************************
' Modul: DieseArbeitsmappe Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Saved Then
Select Case MsgBox("Änderungen speichern?", vbQuestion Or vbYesNoCancel, "Abfrage")
Case vbYes
Call Stop_Timer
Save
Case vbNo
Call Stop_Timer
Saved = True
Case vbCancel
Cancel = True
End Select
End If
End Sub
Private Sub Workbook_Open()
Call Start_Timer
End Sub
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************
Option Explicit
Private ldtmNewStartTime As Date
Public Sub Start_Timer()
If Time >= TimeSerial(6, 0, 0) And Time <= TimeSerial(22, 0, 0) Then
Call Dein_Makro
'next start in 5 minutes
ldtmNewStartTime = Time + TimeSerial(0, 5, 0)
ElseIf Time > TimeSerial(22, 0, 0) And Time < TimeSerial(23, 59, 59) Then
'next start next day 6:00
ldtmNewStartTime = Date + 1 + TimeSerial(6, 0, 0)
Else
'next start this day 6:00
ldtmNewStartTime = TimeSerial(6, 0, 0)
End If
'set delay for next start
Application.OnTime EarliestTime:=ldtmNewStartTime, _
Procedure:="Start_Timer", Schedule:=True
End Sub
Public Sub Stop_Timer()
On Error Resume Next
Application.OnTime EarliestTime:=ldtmNewStartTime, _
Procedure:="Start_Timer", Schedule:=False
End Sub
Public Sub Dein_Makro()
MsgBox "Jetzt läuft's"
End Sub
Gruß
Nepumuk