Basic Excel functionality collides with VBA code.

[Peter Ostermann]

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]

Peter,

Can you post the code?

Naomi


"Peter Ostermann" <[email protected]> wrote in message
news:[email protected]...
> 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]

"Naomi Hildebrand" <[email protected]> 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

[Vic Eldridge]

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]

> "Naomi Hildebrand" <[email protected]> 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

[Peter Ostermann]

"Vic Eldridge" <[email protected]> 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]

"Peter Ostermann" <[email protected]> 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]

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" <[email protected]> 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
> >
> >
> >
>
>

[Peter Ostermann]

"Vic Eldridge" <[email protected]> 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

[Peter Ostermann]

"Vic Eldridge" <[email protected]> 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]

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" <[email protected]> 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
>
>

[Peter Ostermann]

"Vic Eldridge" <[email protected]> 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.)