Delete key causes error in Worksheet_Change in 2003

[Guest]

I have a piece of code which is not working correctly in 2003

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 4 Then
Set TargetCell = Target
Call Procedure1
ElseIf Target.Column = 8 Then
Set TargetCell = Target
Call Procedure2
END Sub


Public TargetCell As Excel.Range

Sub Procedure1

If TargetCell.Value = "" Then
TargetCell.Offset(0, 2).Formula = "=0"
TargetCell.Offset(0, 5).Formula = "=0"
TargetCell.Offset(0, 7).Formula = "=I" & TargetCell.Row
TargetCell.Offset(-94, 2).Formula = "=0"
End Sub
Sub Procedure2

If TargetCell.Value = "" Then
TargetCell.Formula = "=0"
TargetCell.Offset(0, 1).Formula = "=0"
TargetCell.Offset(0, 2).Formula = "=0"
End Sub

In column 8, if I select a blank item from my validation list, or edit the cell and backspace to delete all the characters, this works, but if I press the Delete key, I get a Type Mismatch Error on the line, 'If TargetCell.Value = "" Then'


In column 4, using the Delete Key works fine, but the Offsets do not all work correctly. (0,2) and (0,7) work fine. (0,5) does nothing to remove the previous cell contents, and (-94,2) actually edits the cell at (-94,4) ????

Any ideas gratefully received.


Mahalo

Don

 

[Don Guillett]

Unless I am missing something, this should do the same thing.

if target <> "" and target <> " " then
if target.column=4 then _
target.Offset(0, 7).Formula = "=I" & Target.Row
end if
end sub
--
Don Guillett
SalesAid Software
(e-mail address removed)

I have a piece of code which is not working correctly in 2003

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 4 Then
Set TargetCell = Target
Call Procedure1
ElseIf Target.Column = 8 Then
Set TargetCell = Target
Call Procedure2
END Sub


Public TargetCell As Excel.Range

Sub Procedure1

If TargetCell.Value = "" Then
TargetCell.Offset(0, 2).Formula = "=0"
TargetCell.Offset(0, 5).Formula = "=0"
TargetCell.Offset(0, 7).Formula = "=I" & TargetCell.Row
TargetCell.Offset(-94, 2).Formula = "=0"
End Sub
Sub Procedure2

If TargetCell.Value = "" Then

In column 8, if I select a blank item from my validation list, or edit the

cell and backspace to delete all the characters, this works, but if I press
the Delete key, I get a Type Mismatch Error on the line, 'If
TargetCell.Value = "" Then'

In column 4, using the Delete Key works fine, but the Offsets do not all

work correctly. (0,2) and (0,7) work fine. (0,5) does nothing to remove the
previous cell contents, and (-94,2) actually edits the cell at (-94,4) ????