Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_change event handler error | Excel Discussion (Misc queries) | |||
Compile error: Ambigious name detected: Worksheet_Change **NEWBIE** | Excel Discussion (Misc queries) | |||
Excel 2003 has wrong timing using the worksheet_change macro | Excel Worksheet Functions | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |