Hi experts,
to allow to insert a value in just one of two cells of 2 columns
the "intersect" method of VBA is a solution that works. When
is inserted a number in one of the cells, the corresponding
cell can be set blank by VBA code.

The problem is, that when a number of cells is selected
and at once copied to a range of thaat cells or to a single cell of
the "intersect" controlled columns (or one cell is dragged for
copy purpose to a range of that cells), it does not work.
The content of the corresponding cells isn't set blank
in this case, or even an error happens.

Is there any workaround possible?
Tanks in advance for any hint.

Regards
Peter Ostermann

Peter,

Can you post the code?

Naomi


"Peter Ostermann" wrote in message
...
Hi experts,
to allow to insert a value in just one of two cells of 2 columns
the "intersect" method of VBA is a solution that works. When
is inserted a number in one of the cells, the corresponding
cell can be set blank by VBA code.

The problem is, that when a number of cells is selected
and at once copied to a range of thaat cells or to a single cell of
the "intersect" controlled columns (or one cell is dragged for
copy purpose to a range of that cells), it does not work.
The content of the corresponding cells isn't set blank
in this case, or even an error happens.

Is there any workaround possible?
Tanks in advance for any hint.

Regards
Peter Ostermann

"Naomi Hildebrand" wrote

Peter,

Can you post the code?

Naomi



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).ClearContents
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).ClearContents
End If
Case Else
End Select

I was able to reproduce reported problems only
for the case when copying block of cells and facing
that contents of corresponding cells remains.

Above code may have been changed a bit in the meantime.
I will analyse above code today too. Maybe you see even
a more simple solution anyway?

Regards
Peter
www.pkf-ostermann.de

Hi Peter,

One possibility might be to include a formula somewhere on the worksheet
that refers to your two "controlled" columns. Something like =SUM(A:B) .
With such a formula in place, the worksheet's Calculate event will fire
whenever something is pasted to either of the two columns referred to by the
formula.
Within the Calculate event, you can query the Selection property to to know
which cells are being pasted to, and use Intersect to see if they are within
your "controlled" columns.

eg.

Private Sub Worksheet_Calculate()
If Not Intersect(Columns("A:B"), Selection) Is Nothing Then
MsgBox Selection.Address
End If
End Sub


Regards,
Vic Eldridge





"Peter Ostermann" wrote:

Hi experts,
to allow to insert a value in just one of two cells of 2 columns
the "intersect" method of VBA is a solution that works. When
is inserted a number in one of the cells, the corresponding
cell can be set blank by VBA code.

The problem is, that when a number of cells is selected
and at once copied to a range of thaat cells or to a single cell of
the "intersect" controlled columns (or one cell is dragged for
copy purpose to a range of that cells), it does not work.
The content of the corresponding cells isn't set blank
in this case, or even an error happens.

Is there any workaround possible?
Tanks in advance for any hint.

Regards
Peter Ostermann

"Naomi Hildebrand" wrote

Peter,

Can you post the code?

Naomi



The funny thing is that if it executes
the "Then" twig

If Not IsEmpty(IsEmpty(Pos.Offset(0, 0))) _
And Not IsEmpty(Pos.Offset(0, -1)) _
Then Pos.Offset(0, -1).ClearContents

the "CearContents" ommand doesn't have any
effect.

Regards
Peter

"Vic Eldridge" wrote

Hi Vic,
exellent idea for an alternative method!
The "Sum" Function I have anyway in those
columns. I will try your suggestion.

Regards
Peter

Hi Peter,

One possibility might be to include a formula somewhere on the worksheet
that refers to your two "controlled" columns. Something like =SUM(A:B) .
With such a formula in place, the worksheet's Calculate event will fire
whenever something is pasted to either of the two columns referred to by
the
formula.
Within the Calculate event, you can query the Selection property to to
know
which cells are being pasted to, and use Intersect to see if they are
within
your "controlled" columns.

eg.

Private Sub Worksheet_Calculate()
If Not Intersect(Columns("A:B"), Selection) Is Nothing Then
MsgBox Selection.Address
End If
End Sub


Regards,
Vic Eldridge





"Peter Ostermann" wrote:

Hi experts,
to allow to insert a value in just one of two cells of 2 columns
the "intersect" method of VBA is a solution that works. When
is inserted a number in one of the cells, the corresponding
cell can be set blank by VBA code.

The problem is, that when a number of cells is selected
and at once copied to a range of thaat cells or to a single cell of
the "intersect" controlled columns (or one cell is dragged for
copy purpose to a range of that cells), it does not work.
The content of the corresponding cells isn't set blank
in this case, or even an error happens.

Is there any workaround possible?
Tanks in advance for any hint.

Regards
Peter Ostermann

"Peter Ostermann" wrote

In one worksheet the code is executed as it should do:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Pos As Range

Set Pos = Target

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

' *****setting blank, -- sets blank even a few cells at the same time
'by this single stmt., when a few cells involved !!!! ****

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

But code in the other worksheet does not work (in cases when a
few cells involved), **** even though it is the same code ****.

The only difference is that this sheet contains a number of cells
equipped with a self-written function that "fire" their action as soon as
stmt.
" Pos.Offset(0, 1) = "" " is executed. And by that the original
focus/cell-reference seems to get lost.

Since events and calculation is already deactivated, I wonder
why that bloody function is activated. Has anyone experienced
similar problems?

Regards
Peter

Hi experts,
to allow to insert a value in just one of two cells of 2 columns
the "intersect" method of VBA is a solution that works. When
is inserted a number in one of the cells, the corresponding
cell can be set blank by VBA code.

The problem is, that when a number of cells is selected
and at once copied to a range of that cells or to a single cell of
the "intersect" controlled columns (or one cell is dragged for
copy purpose to a range of that cells), it does not work.
The content of the corresponding cells isn't set blank
in this case, or even an error happens.

Is there any workaround possible?
Tanks in advance for any hint.

Regards
Peter Ostermann

Hi Peter,

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


Regards,
Vic Eldridge


"Peter Ostermann" wrote:


"Peter Ostermann" wrote

In one worksheet the code is executed as it should do:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Pos As Range

Set Pos = Target

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

' *****setting blank, -- sets blank even a few cells at the same time
'by this single stmt., when a few cells involved !!!! ****

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

But code in the other worksheet does not work (in cases when a
few cells involved), **** even though it is the same code ****.

The only difference is that this sheet contains a number of cells
equipped with a self-written function that "fire" their action as soon as
stmt.
" Pos.Offset(0, 1) = "" " is executed. And by that the original
focus/cell-reference seems to get lost.

Since events and calculation is already deactivated, I wonder
why that bloody function is activated. Has anyone experienced
similar problems?

Regards
Peter

Hi experts,
to allow to insert a value in just one of two cells of 2 columns
the "intersect" method of VBA is a solution that works. When
is inserted a number in one of the cells, the corresponding
cell can be set blank by VBA code.

The problem is, that when a number of cells is selected
and at once copied to a range of that cells or to a single cell of
the "intersect" controlled columns (or one cell is dragged for
copy purpose to a range of that cells), it does not work.
The content of the corresponding cells isn't set blank
in this case, or even an error happens.

Is there any workaround possible?
Tanks in advance for any hint.

Regards
Peter Ostermann

"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: =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"
functionfrom 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

"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

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

"Vic Eldridge" wrote


focus/cell-reference seems to get lost.

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


It looked like the "Target" was changed exidentially by the function-exec..
I found the cause now:

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

That are remains from tests and not necessary in the function.
After I commented it out everything runs ok!

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

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

Good hint, stmt. seems to be helpful in every function that is called from
cells.
But in this case it wouldn't have made any difference.


Secondly, you could use a public variable to prevent your function's code
from running

This would have been a solution. Thanks
a lot for assistance.

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