Excel - Test for Insert Row to Trigger the Workbook_Change Event

[beacon5]

Hi everybody,

I'm using Excel 2003 and I'm trying to modify a spreadsheet for work to make it easier to use.

The spreadsheet is basically a project task list where the first row contains the headers and the subsequent rows list the tasks, then the headers are repeated again. This repetition occurs about 10 times.

I've written code to make the tasks, which are identified as Task 1, Task 2, Task 3, etc. (clever, I know) automatically update if a task number is changed using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Worksheets("To-do list").Activate

    If ActiveCell.Column <> 3 Then
        Exit Sub
    End If

    If ActiveCell.Interior.ColorIndex = 47 Then
        Exit Sub
    ElseIf ActiveCell.Offset(-1, 0).Interior.ColorIndex = 47 Then
        ActiveCell.Value = "Task 1"
       While ActiveCell.Offset(1, 0).Interior.ColorIndex <> 47
            ActiveCell.Offset(1, 0).Select
           ActiveCell.Value = "Task " & (Right(ActiveCell.Offset(rowoffset:=-1), 1) + 1)
        Wend
    Else
        ActiveCell.Value = "Task " & (Right(ActiveCell.Offset(rowoffset:=-1), 1) + 1)
        While ActiveCell.Offset(1, 0).Interior.ColorIndex <> 47
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Task " & (Right(ActiveCell.Offset(rowoffset:=-1), 1) + 1)
        Wend
    End If

End Sub

The code looks at the task for a project, tests to see if the it's a header or not, then assigns the task number accordingly by grabbing the task number from the preceding cell/row and concatenating it to the string and repeating only for the tasks under that header (since I'm not an expert in VBA by any stretch of the imagination, I was fairly proud of this code).

The only condition the code above doesn't handle is when a row is inserted. When the row is inserted, I would like the tasks to update just as the do when I change a task.

Anyone have any ideas?

Example of the setup
Status | Project Name | Due Date | Notes |
_______| Task 1 |__________|_______|
_______| Task 2 |__________|_______|
_______| Task 3 |__________|_______|

 

[SkipVought]

Hi,

You might be better off if you constructed your data as a table (better for storage and analysis) rather than as a report (better for visual presentation).
[tt]
Example of the setup
Status | Project Name | Task Name | Due Date | Notes |
_______| Project A | Task 1 |__________|_______|
_______| Project A | Task 2 |__________|_______|
_______| Project A | Task 3 |__________|_______|
_______| Project B | Task 1 |__________|_______|
_______| Project B | Task 2 |__________|_______|
[/tt]
Now you can add a row ANYWHERE for a given project (usually at the BOTTOM of the table), count how many tasks there are for that project (using the COUNTIF function, name the task accordingly and sort into place. Insert ought to be avoided, if at all possible. Pretty simple stuff.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[beacon5]

Hi Skip,

This is the layout that my boss has given me to use, so I have to work within the confines of the template.

So, is there not a way to test for insertion?

Thanks for your help...

 

[SkipVought]

a REPORT (what your boss want to see) is often worlds apart from the data that you need to have to manipulate and analyse.

Using that type of structure as your primary working stucture will eat your lunch eventually.

You could easily use conditional formatting to make the actual data example that I posted above APPEAR like this (but actually look invisible)...
[tt][highlight white]
Status | Project Name | Task Name | Due Date | Notes |
_______| Project A | Task 1 |__________|_______|
_______| [white]Project A[/white] | Task 2 |__________|_______|
_______| [white]Project A[/white] | Task 3 |__________|_______|
_______| Project B | Task 1 |__________|_______|
_______| [white]Project B[/white] | Task 2 |__________|_______|[/highlight]
[/tt]
If you SELECT the text in the example I posted, you'll see ALL the data in the Project Name column.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

However, if your boss cannot be convinced to adhere to spreadsheet best and accepted practices, use the worksheet_change event along with the COUNTA spreadsheet function for the selected row. This example shows the COUNTA result for the given inserted row...

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox Application.CountA(Target.EntireRow) & ":" & Target.Row
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[beacon5]

Yeah she can't be convinced...I tried, trust me. LOL

Your last example seems to be close to what I want to do, but I've not sure how to implement it.

If I want to test it by adding it to my other code, can I do the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.CountA(Target.EntireRow) = 0 then
        ' Line inserted - Do something
    Else
        ' Line deleted - Do something else
    End If
End Sub

Thanks again...

 

[SkipVought]

You have to understand what the rules are for inserting and then being required to enter data. If someone could INSERT and then DELETE, and BOTH results LOOK the same as far as the counts go, you're up the creek.

You ought to be able to use COUNTA for the Target row and also Target.Count, in order to differentiate between an Insert, Delete and a single change.

Play around with these values.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}