add another condition to an event change ?

[StrawberryDreams]

Hi I have a range of three cells (D7:D10) that are being Sum'd and the result is being placed in a "total" cell above them (D6). The VBA is on the worksheet form so that it happens as an event change anytime the range of cells are changed. Everything works great and I would still like to maintain the auto sum capability of the range, but I would like to modify a couple things:

1 - Currently the user can change the "total", cell (D6) value to whatever value they want which I am ok with , ( this way the User can enter their own "total" without having to rely on inputting data into the range as an option), but if they do that , would like the range of cells to clear if they have data.

2 - If the User clears the total in D6, currently data in the range cells remain, and would like those to be cleared as well.

3- How would I write the sum range code more eloquently so that it still places the value of the range in D6 instead of writing a range formula which will place a visible formula in the cell formula bar. Just thinking in cases if I have many cells to add to a range.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim rangeToChange As Range

Set rangeToChange = Range("D7:D10")


'when range of cells are changed, SUM Range total and place result in D6
'Cell D6 is the subtotal of D7:D10

    If Not Intersect(Target, rangeToChange) Is Nothing Then
    
        Range("D6").Value = Range("D7") + Range("D8") + Range("D9") + Range("D10")
        
        End If
        
End Sub

 

[StrawberryDreams]

The "Exit Sub" lines does exactly what you think it does - it exits the code right at that point, if it gets to that line.
The code may work fine with or without it most of the time, but I just wanted to eliminate the possibility of both "blocks" of code ending.
If D6 is updated, the first block is invoked, and there is no need to even check the second one.

However, it is possible that you could update all the cells in D6:D10 at the same time (i.e. through copy/paste, or by clearing all the contents at the same time).
If that happens, that line ensures that only the first block will run.

I appreciate your thinking which employs more bullet-proof coding awareness !

I'm gonna play around with this and see if I can duplicate it to other ranges on the same worksheet. Cheers. Have a good day !

 

[Joe4]

You are welcome.
Glad I was able to help!