Delete key causes error in Worksheet_Change in 2003
 

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

Delete key causes error in Worksheet_Change in 2003
 

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

"Don" wrote in message
...
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
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