Public datumB1 As Variant
Public zellenB4B10 As Variant
Public istSchaltjahr As Boolean
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim wsDeckblatt As Worksheet
Dim wsStudien As Worksheet
Dim rng As Range
Dim cell As Range
Dim i As Integer
Set ws = ThisWorkbook.Sheets("Deckblatt")
Set wsDeckblatt = ThisWorkbook.Sheets("Deckblatt")
Set wsStudien = ThisWorkbook.Sheets("Studien")
Set rng = ws.Range("B4:B10")
If IsEmpty(ws.Range("B1").Value) And _
IsEmpty(ws.Range("B4").Value) And _
IsEmpty(ws.Range("B5").Value) And _
IsEmpty(ws.Range("B6").Value) And _
IsEmpty(ws.Range("B7").Value) And _
IsEmpty(ws.Range("B8").Value) And _
IsEmpty(ws.Range("B9").Value) And _
IsEmpty(ws.Range("B10").Value) Then
MsgBox "Bitte tragen Sie ein Datum in Zelle B1 und die Namen der Studie ein. Speichern Sie die Änderung. Schließen Sie die Arbeitsmappe und öffnen Sie die Arbeitsmappe wieder, damit die Änderungen übernommen werden."
End If
If IsNumeric(ws.Range("B1").Value) Then
datumB1 = CInt(ws.Range("B1").Value)
ws.Range("C1").Value = datumB1
istSchaltjahr = False
If (datumB1 Mod 4 = 0 And datumB1 Mod 100 <> 0) Or datumB1 Mod 400 = 0 Then
istSchaltjahr = True
End If
ws.Range("E1").Value = istSchaltjahr
End If
For Each cell In rng
i = cell.Row - 3
On Error Resume Next
If Not IsEmpty(cell.Value) Then
zellenB4B10 = zellenB4B10 & " " & cell.Address
ws.Range("D1").Value = zellenB4B10
ThisWorkbook.Sheets("Studie_" & i).Visible = True
Else
ThisWorkbook.Sheets("Studie_" & i).Visible = False
End If
On Error GoTo 0
Next cell
Set wsStudien = ThisWorkbook.Sheets("Studien")
zellenB4B10 = wsDeckblatt.Range("D1").Value
If IsEmpty(zellenB4B10) Then
For i = 1 To 7
wsStudien.Rows(i + 7).EntireRow.Hidden = True
Next i
Else
Dim zellen As Variant
zellen = Split(zellenB4B10, " ")
For Each zelle In zellen
If zelle <> "" Then
i = Right(zelle, 1) - 3
wsStudien.Rows(i + 7).EntireRow.Hidden = False
End If
Next zelle
End If
End Sub