How using multiple columns in worksheet_change event

[Abdo]

Hello
I need any function to combine multiple columns when I add the values .
my goal when I add values whether in column C or D , then should call my macro . the headers starts from row1 ,then should implement from row2 when add values. this is what I have .

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Columns("C")) Is Nothing Then Call test
If Target.CountLarge > 1 Then Exit Sub

End Sub

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Check to see if updated cell is in columns C or D after row 2
    If (Target.Column = 3 Or Target.Column = 4) And (Target.Row > 1) Then
        Application.EnableEvents = False
        Call test
        Application.EnableEvents = True
    End If

End Sub

Note that it does no good to run the "CountLarge" line after you have already called/run the code!
That is a preliminary check to make sure that only one cell has been updated.

 

[Abdo]

Note that it does no good to run the "CountLarge" line after you have already called/run the code!
That is a preliminary check to make sure that only one cell has been updated.

ok
awesome! this is waht look for it.
many thanks

 

[Joe4]

You are welcome!
I hope it all makes sense. If not, feel free to ask any questions.

Note that you might be wondering about this line:

        Application.EnableEvents = False

This temporarily disables all automate event procedures (like "Worksheet_Change") from running.
You want to use this if your "Test" procedure is updating any cells in column C or D (otherwise, that will trigger the "Worksheet_Change" code to run again, and if you aren't careful, you can get caught in an endless loop!)

So, it is common to temporarily disable that code from calling itself. Then, when you are done with all the changes, you set it back with a line like this:

        Application.EnableEvents = True

so that it will work next time.

If your "Test" procedure is not updating any cells in columns C or D, then you don't need those lines (though it doesn't hurt anything to leave them in there).

Also note that if there is an error in your code, such that the "... = False" line runs, but it never gets to the "... = True" line to turn it back on, then the automated procedures won't work until you turn them back on (most commonly happens while testing and working through issues). If that happens, you can easily turn them back on again by manually running this short procedure:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

 

[Abdo]

thanks for all of clarification .

You want to use this if your "Test" procedure is updating any cells in column C or D (otherwise, that will trigger the "Worksheet_Change" code to run again, and if you aren't careful, you can get caught in an endless loop!)

often will happen in many time becuase maybe wrong entering values , then should correct again .
thanks again buddy