Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Private Sub Worksheet_Change code not robust enough
I have a file with data in columns A, B and C. For each row, the data
in column B is dependent on the data in column A. Therefore, if someone changes a cell in column A, or deletes the entry, I want the entry in the same row of column B to be deleted. I have the code below. It works when I hit Delete in column A, but when I hit Backspace, it either jumps over to column C and deletes the entry there, or jumps down 1 row and deletes the entry in column B. Thank you for the assistance. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 then Exit Sub End If If Target.Column = 1 ActiveCell.Offset(0, 1).Select Selection.ClearContent End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Private Sub Worksheet_Change code not robust enough
Maybe...
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) if target.cells.count 1 then exit sub 'one cell at a time If Target.Column < 1 then Exit Sub 'stop this procedure from calling itself application.enableevents = false target.offset(0,1).clearcontents application.enableevents = true End Sub GI wrote: I have a file with data in columns A, B and C. For each row, the data in column B is dependent on the data in column A. Therefore, if someone changes a cell in column A, or deletes the entry, I want the entry in the same row of column B to be deleted. I have the code below. It works when I hit Delete in column A, but when I hit Backspace, it either jumps over to column C and deletes the entry there, or jumps down 1 row and deletes the entry in column B. Thank you for the assistance. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 then Exit Sub End If If Target.Column = 1 ActiveCell.Offset(0, 1).Select Selection.ClearContent End If End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Private Sub Worksheet_Change code not robust enough
Try this instead:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 Then Exit Sub End If If Target.Column = 1 Then Application.EnableEvents = False Target.Offset(0, 1).ClearContents Application.EnableEvents = True End If End Sub --JP On Jul 31, 12:58*pm, GI wrote: I have a file with data in columns A, B and C. *For each row, the data in column B is dependent on the data in column A. *Therefore, if someone changes a cell in column A, or deletes the entry, I want the entry in the same row of column B to be deleted. I have the code below. It works when I hit Delete in column A, but when I hit Backspace, it either jumps over to column C and deletes the entry there, or jumps down 1 row and deletes the entry in column B. *Thank you for the assistance. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 then Exit Sub End If If Target.Column = 1 * * ActiveCell.Offset(0, 1).Select * * Selection.ClearContent * * End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Private Sub Worksheet_change ... Procedures jamming up | Excel Programming | |||
Private Sub Worksheet_Change doesn't work? | Excel Programming | |||
Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming | |||
Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming | |||
Can someone help me make this code more robust? | Excel Programming |