Macro worksheet change

[MarkReddell]

I have several actions that need to be done within a worksheet change scenario. The Problem is that the whole macro runs instead of just the part that needs to work because of a cell change & it also goes into a loop or something. How do I run only the part of macro that needs to instead of ALL of macro? Here is what I'm trying to run:

Can anyone help me with this??? Thanx!!!

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(False, False) = "A1" And Target.Value <> "" Then Call SORT_LIST
If Target.Address(False, False) = "A2" And Target.Value <> "" Then Call SORT_LIST
If Target.Address(False, False) = "A3" And Target.Value <> "" Then Call SORT_LIST
If Target.Address(False, False) = "A4" And Target.Value <> "" Then Call SORT_LIST
If Target.Address(False, False) = "A5" And Target.Value <> "" Then Call SORT_LIST
If Range("A17").Value = "YES" Then Call SHOW_TERMS_ONLY
If Range("A17").Value = "NO" Then Call DONT_SHOW_TERMS_ONLY
If Target.Address <> "$B$2" Then Exit Sub
Application.Run Target.Value
End Sub

 

[wigi]

This might bring you closer to what you want, but will need testing and modifying surely:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Target, Range("A1:A5")) Is Nothing Then
        If Target.Value <> "" Then Call SORT_LIST
    End If
    
    If Range("A17").Value = "YES" Then
        Call SHOW_TERMS_ONLY
    ElseIf Range("A17").Value = "NO" Then
        Call DONT_SHOW_TERMS_ONLY
    End If
    
    If Target.Address = "$B$2" Then Application.Run Target.Value

End Sub

 

[MarkReddell]

Thanx 4 your reply & help!!! It works expect this part of macro! When I run a macro command from B2, it doesn't run those without causing debug problem that doesn't happen if I don't use it within the worksheet change!!! I think the problem is that the code below tries to run also!


If Target.Address = "$B$2" Then Application.Run Target.Value

 

[Anthony47]

Not sure I understood your point, however in addition to wigi suggestion I recommend to insert Application.EnableEvents = False at the beginning of the macro and Application.EnableEvents = True before exiting; for example:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False    ' ADD
If Target.Address(False, False) = "A1" And Target.Value <> "" Then Call SORT_LIST
If Target.Address(False, False) = "A2" And Target.Value <> "" Then Call SORT_LIST
If Target.Address(False, False) = "A3" And Target.Value <> "" Then Call SORT_LIST
If Target.Address(False, False) = "A4" And Target.Value <> "" Then Call SORT_LIST
If Target.Address(False, False) = "A5" And Target.Value <> "" Then Call SORT_LIST
If Range("A17").Value = "YES" Then Call SHOW_TERMS_ONLY
If Range("A17").Value = "NO" Then Call DONT_SHOW_TERMS_ONLY
If Target.Address = "$B$2" Then Application.Run Target.Value  'MODIFIED
Application.EnableEvents = True    'ADD
End Sub

This should prevent the macro from looping in case the called macro itsel modify the sheet, as I suspect.

Bye

 

[MarkReddell]

Thanx 2 both wigi & Anthony47!!! This worx perfectly!!!!!!!!!!

 

[MarkReddell]

oops!!! How do I fix the problem with the macro "Sort_List" from running ever time a make a worksheet change in other unrelated cells???

 

[Anthony47]

This requires following wigi suggestion:

Private Sub Worksheet_Change(ByVal Target As Range)
CheckArea="A1:A5"     'The area to be monitored PUT YOUR VALUE
If Application.Intersect(Target, Range(CheckArea)) Is Nothing Then Exit Sub
Application.EnableEvents = False    ' ADD
If Target.Address(False, False) = "A1" And Target.Value <> "" Then Call SORT_LIST
'etc etc

Bye