"Vic Eldridge" wrote:

Hi Peter,

You mentioned that events were already deactivated.
If that were truly the case, how does your Worksheet_Change code get
triggered ?



Vic,
I did not post the complete code before.
Sorry for confusion. I will post it now. See below.
Events are deactivated right in the *event* procedure.
That is why I wonder that my function

sample also below: =AbweichungenEinpflegen(E5;$C5;$D5;Varianten!C5)

which is errorfree itself and which is called from more than 300
cells, gets activated and is causing the error, even though events
and calculation are not yet enabled at that time.

I just tested the matter again: If I delete my "AbweichungenEinpflegen"
function from all the cells first, then everything runs errorfree as
expected.

Giving up and reproducing the function by the Excel
Spaghetti-code like nested "IF", "AND", "OR" right in the cell,
I do not accept and it shouldn't be the solution in this case.
I better like drilling down and find the cause of the malfunction.
Maybe with your help ?! ;-)

Regards
Peter
PS. By the way another question:
Is it possible to unprotect cells but keep the
"format" (like lines surrounding the cells, etc.)
protected?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Pos As Range
Dim Zeile
Dim Spalte
Dim Zeile2
Dim Spalte2
Dim x
Dim Calc
Dim Change
Dim Scrupd

If Application.Intersect(Target, [AusgabePeriodisch]) Is Nothing _
And Application.Intersect(Target, [EinnahmePeriodisch]) Is Nothing _
Then Exit Sub

Change = Application.EnableEvents
Scrupd = Application.ScreenUpdating
Calc = Application.Calculation

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlManual

On Error GoTo Ende

Set Pos = Target
Zeile = Pos.Row
Spalte = Pos.Column

Call SchutzEntfernen(ActiveSheet)

Select Case (True)
Case Not Application.Intersect(Target, [AusgabePeriodisch]) Is
Nothing
If Not Application.Intersect(Target, [AusgabePeriodisch]) Is
Nothing _
And Not Application.Intersect(Target, [EinnahmePeriodisch]) Is
Nothing _
Then
Else
If Not IsEmpty(Pos.Offset(0, 0)) _
And Not IsEmpty(Pos.Offset(0, 1)) _
Then Pos.Offset(0, 1) = ""
End If
Case Not Application.Intersect(Target, [EinnahmePeriodisch]) Is
Nothing
If Not Application.Intersect(Target, [AusgabePeriodisch]) Is
Nothing _
And Not Application.Intersect(Target, [EinnahmePeriodisch]) Is
Nothing _
Then
Else
If Not IsEmpty(IsEmpty(Pos.Offset(0, 0))) _
And Not IsEmpty(Pos.Offset(0, -1)) _
Then Pos.Offset(0, -1) = ""
End If
Case Else
End Select

Call JahresZahlenErmitteln
Ende:
Call SchutzSetzen(ActiveSheet, Z_Protect)
Application.EnableEvents = Change
Application.ScreenUpdating = Scrupd
Application.Calculation = Calc
End Sub

Public Function AbweichungenEinpflegen(Faelligkeit, Ausgabe, Einnahme,
Variante)
Dim Pos As Range
Dim Adresse
Dim Z_Faelligkeit
Dim Z_Ausgabe
Dim Z_Einnahme
Dim Z_Variante

Set Pos = Application.Caller
Adresse = Application.Caller.Address

Z_Faelligkeit = Faelligkeit
Z_Ausgabe = Ausgabe
Z_Einnahme = Einnahme
Z_Variante = Variante

Select Case (True)
Case Z_Faelligkeit = ""
AbweichungenEinpflegen = ""

Case Z_Ausgabe < "" And Z_Variante = ""
AbweichungenEinpflegen = -Z_Ausgabe

Case Z_Ausgabe < "" And Z_Variante < ""
AbweichungenEinpflegen = -Z_Variante

Case Z_Einnahme < "" And Z_Variante = ""
AbweichungenEinpflegen = Z_Einnahme

Case Z_Einnahme < "" And Z_Variante < ""
AbweichungenEinpflegen = Z_Variante

Case Else
AbweichungenEinpflegen = ""
End Select
End Function