Problem with Private Sub Worksheet_Change(ByVal Target As Range), runs all subs

[DavyJones90]

I have a problem with Private Sub Worksheet_Change(ByVal Target As Range), as I have a lot of code in my workbook, I had to split it into several parts an Target them

So every time there is a change in the workbook it runs through 14 different subs

EURUSDTarget Target
AUDUSDTarget Target
GBPUSDTarget Target
EURGBPTarget Target
USDCHFTarget Target
EURCHFTarget Target
USDJPYTarget Target
AUDJPYTarget Target
EURJPYTarget Target
GBPJPYTarget Target
DXYTarget Target
XAUTarget Target
LCOC1Target Target
ZARTarget Target

Is there a way how it will stop targetting the next sub if one of them executed, till the next time a cell value changes?

 

[davesexcel]

if target.address=$A$1 then
'do this
end if
if target.address=$B$1 then
'do that
end if
''etc

 

[DavyJones90]

Thanks but that is my exact problem, I have about 5000 fields which can change with target adress. This is why I split the code into 14 Subs and Call on the subs over Private Sub Worksheet_Change(ByVal Target As Range), the problem with this is, that the sheet runs through all 14 subs, even if only Sub 1 is applicable, which is why the loading time after every change is very long. What I need is that it stops going to the next sub when a value has effectively changed, till the next value is changed.

 

[davesexcel]

Oh ya, so running the first one that changes the sheet, worksheet_change activates again?

application.EnableEvents =false will stop the worksheet change from kicking in until after the code has run

for example

if target.address=$A$1 then
application.EnableEvents =false
'do this
application.EnableEvents =true
end if
if target.address=$B$1 then
application.EnableEvents =false
'do that
application.EnableEvents =true
end if
''etc

 

[DavyJones90]

is there a way of doing this on sub level, I am so not interested in changing my several hundred if statements ?

Something like (Fake Code)

If  EURUSDTarget Target Then
    application.EnableEvents =false
    Call EURUSDTarget
    application.EnableEvents =true
ElseIf  AUDUSDTarget Target Then
    application.EnableEvents =false
    
etc. etc. etc.
Else
End If

 

[DavyJones90]

I found a work around

If Target.Row >= 1 <= 33 Then

did the trick

 

[smozgur]

If Target.Row >= 1 <= 33 Then

The Target.Row value is always greater than or equal to 1 and this code line simply means (TRUE) <= 33 which will always return TRUE since TRUE is -1 and -1 <= 33. Even you used Target.Row >= 10 (or something else bigger than 1), then it might have returned FALSE in that case but still means (FALSE) <= 33 which still returns TRUE since FALSE is 0 and 0 <= 33.

Basically, this code line always returns TRUE and I can't see how it does the trick, therefore, I removed the marked solution from that post.

Even you meant the following with "And" (ignoring the unnecessary first comparison in the code line):

If Target.Row >= 1 And Target.Row <= 33 Then

However, it is still not obvious how this solves the question.

Perhaps this could be an explanation (with "And" that I posted above): You want to execute the Worksheet_Change procedure only when a cell between Row 1 and Row 33 is changed, but then other sub-routines must not be changing any cell value in that range. You should also keep in mind that if other subs are changing too many cells (5000?), then even if the changed cells are not in that range, each of them will still hit the Worksheet_Change event to execute that conditional line that will slow down your code.

@davesexcel's solution on the other hand, (Application.EnableEvents), looks like a good suggestion that could be implemented in order to solve the original question by setting the Application.EnableEvents property False at the beginning and set it back to True at the end of the procedure.

Basically, it could be something like this.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Row > 10 And Target.Row <= 15 Then
    ' call a sub routine
ElseIf Target.Row > 15 And Target.Row <= 30 Then
    ' call another sub routine
End If
Application.EnableEvents = True
End Sub

So, if you could explain your exact solution that solves the problem, then it is perfectly fine to mark your post as the solution as it will also help future readers. Otherwise (and if the Application.EnableEvents solution that daveexcel suggested doesn't help to solve the problem for some reason, even with the possible implementation I posted above), please do not mark a post that doesn't include a solution.

 

[DavyJones90]

The Target.Row value is always greater than or equal to 1 and this code line simply means (TRUE) <= 33 which will always return TRUE since TRUE is -1 and -1 <= 33. Even you used Target.Row >= 10 (or something else bigger than 1), then it might have returned FALSE in that case but still means (FALSE) <= 33 which still returns TRUE since FALSE is 0 and 0 <= 33.

Basically, this code line always returns TRUE and I can't see how it does the trick, therefore, I removed the marked solution from that post.

Even you meant the following with "And" (ignoring the unnecessary first comparison in the code line):

However, it is still not obvious how this solves the question.

Perhaps this could be an explanation (with "And" that I posted above): You want to execute the Worksheet_Change procedure only when a cell between Row 1 and Row 33 is changed, but then other sub-routines must not be changing any cell value in that range. You should also keep in mind that if other subs are changing too many cells (5000?), then even if the changed cells are not in that range, each of them will still hit the Worksheet_Change event to execute that conditional line that will slow down your code.

@davesexcel's solution on the other hand, (Application.EnableEvents), looks like a good suggestion that could be implemented in order to solve the original question by setting the Application.EnableEvents property False at the beginning and set it back to True at the end of the procedure.

Basically, it could be something like this.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Row > 10 And Target.Row <= 15 Then
    ' call a sub routine
ElseIf Target.Row > 15 And Target.Row <= 30 Then
    ' call another sub routine
End If
Application.EnableEvents = True
End Sub

So, if you could explain your exact solution that solves the problem, then it is perfectly fine to mark your post as the solution as it will also help future readers. Otherwise (and if the Application.EnableEvents solution that daveexcel suggested doesn't help to solve the problem for some reason, even with the possible implementation I posted above), please do not mark a post that doesn't include a solution.

The Target.Row value is always greater than or equal to 1 and this code line simply means (TRUE) <= 33 which will always return TRUE since TRUE is -1 and -1 <= 33. Even you used Target.Row >= 10 (or something else bigger than 1), then it might have returned FALSE in that case but still means (FALSE) <= 33 which still returns TRUE since FALSE is 0 and 0 <= 33.

Basically, this code line always returns TRUE and I can't see how it does the trick, therefore, I removed the marked solution from that post.

Even you meant the following with "And" (ignoring the unnecessary first comparison in the code line):

However, it is still not obvious how this solves the question.

Perhaps this could be an explanation (with "And" that I posted above): You want to execute the Worksheet_Change procedure only when a cell between Row 1 and Row 33 is changed, but then other sub-routines must not be changing any cell value in that range. You should also keep in mind that if other subs are changing too many cells (5000?), then even if the changed cells are not in that range, each of them will still hit the Worksheet_Change event to execute that conditional line that will slow down your code.

@davesexcel's solution on the other hand, (Application.EnableEvents), looks like a good suggestion that could be implemented in order to solve the original question by setting the Application.EnableEvents property False at the beginning and set it back to True at the end of the procedure.

Basically, it could be something like this.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Row > 10 And Target.Row <= 15 Then
    ' call a sub routine
ElseIf Target.Row > 15 And Target.Row <= 30 Then
    ' call another sub routine
End If
Application.EnableEvents = True
End Sub

So, if you could explain your exact solution that solves the problem, then it is perfectly fine to mark your post as the solution as it will also help future readers. Otherwise (and if the Application.EnableEvents solution that daveexcel suggested doesn't help to solve the problem for some reason, even with the possible implementation I posted above), please do not mark a post that doesn't include a solution.

Hi Smozgur, what you said sounds, very smart, I just don't get it. Rereading my post I can see how you can't see that it solves the problem though. Anyhow, whether it solves it or not, since I made the change, my sheet is a lot faster and works fine so I'll leave it as is for now, but might change it when I got time, to what you said, as it seems more proper.

The reason the Target.Row function solves the problem is because my forms stack on top of eachother, and each part of my code, only runs on one of the forms.