AW: Datum Format
15.04.2019 16:57:53
Nepumuk
Hallo Josi,
in das Modul der Tabelle (Rechtsklick auf den Tabellenreiter - Code anzeigen):
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim objRange As Range, objCell As Range
Set objRange = Intersect(Target, Range("D4:D250"))
If Not objRange Is Nothing Then
Application.EnableEvents = False
For Each objCell In objRange
If IsNumeric(objCell.Value2) Then If objCell.Value <= DaysInMonth(Year(Date), Month(Date)) Then _
objCell.Value = DateSerial(Year(Date), Month(Date), objCell.Value)
Next
Application.EnableEvents = True
End If
End Sub
Private Function DaysInMonth(ByVal pvintYear As Integer, ByVal pvintMonth As Integer) As Long
Select Case pvintMonth
Case 1, 3, 5, 7, 8, 10, 12
DaysInMonth = 31
Case 4, 6, 9, 11
DaysInMonth = 30
Case 2
If pvintYear Mod 4 = 0 And (pvintYear Mod 100 <> 0 Or pvintYear Mod 400 = 0) Then
DaysInMonth = 29
Else
DaysInMonth = 28
End If
End Select
End Function
Gruß
Nepumuk