Basic Excel functionality collides with VBA code.
"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
|