Option
Explicit
Private
Sub
Workbook_BeforeClose(Cancel
As
Boolean
)
Dim
Bereich
As
Range
Dim
Antwort
As
String
With
ThisWorkbook.Worksheets(
"Tabelle1"
)
Set
Bereich = .Range(
"d15:d40"
)
.Range(
"d41"
).Value = Application.WorksheetFunction.Sum(Bereich)
If
.Range(
"g2"
).Value <>
"geprüft"
Then
If
.Range(
"d41"
) >= 90
Then
MsgBox
"Heizung prüfen! (>= 90 Tage)"
, vbOKOnly + vbExclamation
Antwort = MsgBox(
"Wurde die Heizung geprüft?"
, vbYesNoCancel + vbQuestion)
If
Antwort = vbYes
Then
.Range(
"g2"
).Value =
"geprüft"
.Range(
"h2"
).Value = VBA.Environ(
"UserName"
)
.Range(
"i2"
).Value = Format(Now,
"DD.MM.YYYY_HH:MM:SS"
)
.Range(
"g2:i2"
).Interior.ColorIndex = 4
.Range(
"g10"
).Value = InputBox(
"Welche Firma?"
)
Do
Antwort = InputBox(
"Wann geprüft? Bitte Datum eingeben!"
,
"Datum TT.MM.JJJJ"
, _
FormatDateTime(
Date
, vbShortDate))
Loop
Until
Antwort <>
""
And
IsDate(Antwort)
.Range(
"g13"
).Value = Format(Antwort,
"DD.MM.YYYY"
)
ElseIf
Antwort = vbNo
Then
.Range(
"g2"
).Value =
"NICHT geprüft!!!"
.Range(
"h2"
).Value = VBA.Environ(
"UserName"
)
.Range(
"i2"
).Value = Format(Now,
"DD.MM.YYYY_HH:MM:SS"
)
.Range(
"g2:i2"
).Font.Bold =
True
.Range(
"g2:i2"
).Interior.ColorIndex = 3
Else
MsgBox
"Warum brichst Du ab?"
End
If
End
If
End
If
End
With