Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim wsh As Worksheet
Dim nm As Name
Dim rngChk As Range
Dim bProtect As Boolean
Dim iMonth As Integer
If TypeName(Sh) = "Worksheet" Then
Set wsh = Sh
Set nm = GetName(wsh, "Eingaben")
If Not nm Is Nothing Then
iMonth = GetMonthFromWsh(wsh)
If Not iMonth = 0 Then
If iMonth >= Month(Date) Or (Day(Date) <= 5 And iMonth + 1 = Month(Date)) Then
bProtect = False
Else
bProtect = True
End If
wsh.Unprotect
For Each rngChk In nm.RefersToRange.Cells
rngChk.Locked = bProtect
Next
wsh.Protect
End If
End If
End If
End Sub
Function GetMonthFromWsh(wsh As Worksheet) As Integer
Dim iMonth As Integer
For iMonth = 1 To 12
If wsh.Name = Format(DateSerial(Year(Date), iMonth, 1), "MMMM") Then
GetMonthFromWsh = iMonth
End If
Next
End Function
Function GetName(wsh As Worksheet, sName As String) As Name
Dim nm As Name
For Each nm In wsh.Names
If nm.Name = wsh.Name & "!" & sName Then
Set GetName = nm
End If
Next
End Function