Option Explicit
Sub CheckCellProtect()
Dim rngTab As Range, rngRow As Range
Set rngTab = getTransaction()
For Each rngRow In rngTab.Rows
SetProtectRow rngRow, IsTableRowComplete(rngRow)
Next
End Sub
Function getTransaction() As Range
Set getTransaction = ActiveWorkbook.Names("Transaktionen").RefersToRange
End Function
Function getTransactionData() As Range
Set getTransactionData = ActiveWorkbook.Names("TransaktionDaten").RefersToRange
End Function
Function IsTableRowComplete(rngRow As Range) As Boolean
Dim rng As Range
IsTableRowComplete = True
For Each rng In rngRow.Cells
If IsEmpty(rng) Then
IsTableRowComplete = False
Exit For
End If
Next
End Function
Sub SetProtectRow(rngRow As Range, Statuslocked As Boolean)
Dim rng As Range
ProtectSheet rngRow.Worksheet, False
For Each rng In rngRow.Cells
rng.Locked = Statuslocked
Next
ProtectSheet rngRow.Worksheet, True
End Sub
Sub ProtectSheet(sh As Worksheet, Protection As Boolean)
If sh.ProtectContents = True And Protection = False Then
sh.Unprotect Password:="DeinPasswort"
ElseIf sh.ProtectContents = False And Protection = True Then
sh.Protect Password:="DeinPasswort", UserInterfaceOnly:=True
End If
End Sub
Function ColumnStorno(rng As Range) As Boolean
Dim rngTab As Range
Dim rngActive As Range
Set rngTab = getTransaction()
If Not Intersect(rng, rngTab) Is Nothing Then
Set rngActive = ActiveCell
If Not Intersect(rng.EntireRow, rngActive, GetStornoColumn) Is Nothing Then
ColumnStorno = True
End If
End If
End Function
Function GetStornoColumn()
Dim rngTab As Range, rngTitle As Range, rngStorno As Range
Set rngTab = getTransaction()
Set rngTitle = rngTab
Set rngStorno = rngTitle.Find(what:="Storno", LookIn:=xlValues, LookAt:=xlWhole)
If Not rngStorno Is Nothing Then
Set rngStorno = Intersect(rngStorno.EntireColumn, rngTab)
End If
Set GetStornoColumn = rngStorno
End Function