Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Three Cases
Hi,
Im trying to write code so that if i 1) Insert a value in col A a row is inserted below 2) If i then go back and delete this value the row below is deleted 3) If I go back and change the value the no of rows remain With the code below rows are inserted in all three cases, many thanks for help Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit: With Target If .Column = 1 Then If .Cells.Count = 1 Then .Offset(1).EntireRow.Insert xlShiftDown End If End If End With ws_exit: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Three Cases
Teresa,
Stick to one thread it makes life simpler to see what has already gone down. This is difficult, how is the code supposed to know that a row has been inserted by this code, or that a cell has already been changed. One way would be to have a column with a value in there, which can handle conditions 1 and 3, but not 2. Best to select both rows to delete that. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit: With Target If .Column = 1 Then If .Cells.Count = 1 Then If .Offset(0, 1).Value < "Upd" Then .Offset(0, 1).Value = "Upd" .Offset(1).EntireRow.Insert xlShiftDown .Offset(1, 1).Value = "Insert" End If End If End If End With ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips "teresa" wrote in message ... Hi, Im trying to write code so that if i 1) Insert a value in col A a row is inserted below 2) If i then go back and delete this value the row below is deleted 3) If I go back and change the value the no of rows remain With the code below rows are inserted in all three cases, many thanks for help Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit: With Target If .Column = 1 Then If .Cells.Count = 1 Then .Offset(1).EntireRow.Insert xlShiftDown End If End If End With ws_exit: Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for Cases=144 * # of Cases + Pieces | Excel Discussion (Misc queries) | |||
repetition cases | Excel Discussion (Misc queries) | |||
two cases for countif | Excel Worksheet Functions | |||
Cases | Excel Programming | |||
combobox cases | Excel Programming |