Hi Peter,

as soon as stmt.
" Pos.Offset(0, 1) = "" " is executed. And by that the original
focus/cell-reference seems to get lost.

I'm not sure what you mean by the above statement. What exactly is going
wrong ?

At this point, there's two things I'd try.

Firstly, have a play with Application.Volatile and see if that makes any
difference.

Secondly, you could use a public variable to prevent your function's code
from running when it's not wanted. Set the variable to False just before
your Worksheet_Change code writes to the worksheet, and then set it back to
True immediately after the writing command. Then, in the first line of your
function, you would look at that variable and exit the function if the
variable was False.



PS. By the way another question:
Is it possible to unprotect cells but keep the
"format" (like lines surrounding the cells, etc.)
protected?
In Excel 2003, you can. The Protect Sheet dialog has a whole list of
individual components you can choose to protect or not. IIRC this was not
possible in earlier versions. Having said that, with regards to border lines,
if the border lines were applied to the adjacent cells, those adjacent cells
could be protected. It might not work though if you're dealing with
multi-cell ranges.


Regards,
Vic Eldridge



"Peter Ostermann" wrote:

"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