Run Multiple Calls every Worksheet_SelectionChange

[eli_m]

Hi,

I've pasted the full code below but the part I need help with is:

' Hide Row - Column AQ with "Hide Row"
    If Not Intersect(Target, Range("AQ:AQ")) Is Nothing And InStr(ActiveCell.Value, "Hide Row") > 0 Then
            Call HideRow.HideRow
    End If

' CaseLink - Confirm - Column T
    If Not Intersect(Target, Range("T:T")) Is Nothing Then
            Call CaseLink_Pre.CaseLink_Pre
    End If

' CaseLink - Check - Column AJ
    If Not Intersect(Target, Range("AJ:AJ")) Is Nothing Then
            Call CaseLink_Check.CaseLink_Check
    End If

' CaseLink - Confirm - Column AC
    If Not Intersect(Target, Range("AC:AC")) Is Nothing Then
            Call CaseLink_Confirm.CaseLink_Confirm
    End If

' Create Calendar Invite - Column U Only
    If Not Intersect(Target, Range("U:U")) Is Nothing Then
        If InStr(ActiveCell.Value, "Create Calendar Invite") > 0 Then
            Call CalendarInvite.CalendarInvite
    End If

How can I make each section of this run in sequence and if one "fails" it goes onto the next.

Currently I need to have it in this order for it to work. If I put this last:

' CaseLink - Confirm - Column T
    If Not Intersect(Target, Range("T:T")) Is Nothing Then
            Call CaseLink_Pre.CaseLink_Pre
    End If

Then some of the other "Calls" stops working.

I can't figure out for the life of me how to fix it.


Full Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Check Timeout timer
checktime = True
Lastchange = Now()

' Refresh for Grey Line
If Application.CutCopyMode = False Then
Application.Calculate
End If

' Exit if more than one cell is selected
If Target.CountLarge > 1 Then Exit Sub

' Hide Row - Column AQ with "Hide Row"
    If Not Intersect(Target, Range("AQ:AQ")) Is Nothing And InStr(ActiveCell.Value, "Hide Row") > 0 Then
            Call HideRow.HideRow
    End If

' CaseLink - PreBook - Column T
    If Not Intersect(Target, Range("T:T")) Is Nothing Then
            Call CaseLink_Pre.CaseLink_Pre
    End If

' CaseLink - Check - Column AJ
    If Not Intersect(Target, Range("AJ:AJ")) Is Nothing Then
            Call CaseLink_Check.CaseLink_Check
    End If

' CaseLink - Confirm - Column AC
    If Not Intersect(Target, Range("AC:AC")) Is Nothing Then
            Call CaseLink_Confirm.CaseLink_Confirm
    End If

' Create Calendar Invite - Column U Only
    If Not Intersect(Target, Range("U:U")) Is Nothing Then
        If InStr(ActiveCell.Value, "Create Calendar Invite") > 0 Then
            Call CalendarInvite.CalendarInvite
    End If

End If

End Sub

 

[StephenCrump]

You exit the Sub if more than one cell is selected. And the ranges you check for an Intersect are all different columns, hence the Sub will make either one "Call" or no "Call"s depending on which cell is selected. The order of the Intersect tests shouldn't matter.

Given your thread heading is "Run Multiple Calls every Worksheet_SelectionChange", it looks like you wanted to do something different? Perhaps you could explain?

 

[bebo021999]

Do you want to select multiple ranges, for example U and T, and then call the corresponding subs for each range U followed by T?

 

[eli_m]

You exit the Sub if more than one cell is selected. And the ranges you check for an Intersect are all different columns, hence the Sub will make either one "Call" or no "Call"s depending on which cell is selected. The order of the Intersect tests shouldn't matter.

Given your thread heading is "Run Multiple Calls every Worksheet_SelectionChange", it looks like you wanted to do something different? Perhaps you could explain?

Thanks for getting back to me.

If multiple cells are selected then I want it to Exit the sub and NOT do the below. I only want it to run the below code when 1 cell is selected:

' Hide Row - Column AQ with "Hide Row"
    If Not Intersect(Target, Range("AQ:AQ")) Is Nothing And InStr(ActiveCell.Value, "Hide Row") > 0 Then
            Call HideRow.HideRow
    End If

' CaseLink - Confirm - Column T
    If Not Intersect(Target, Range("T:T")) Is Nothing Then
            Call CaseLink_Pre.CaseLink_Pre
    End If

' CaseLink - Check - Column AJ
    If Not Intersect(Target, Range("AJ:AJ")) Is Nothing Then
            Call CaseLink_Check.CaseLink_Check
    End If

' CaseLink - Confirm - Column AC
    If Not Intersect(Target, Range("AC:AC")) Is Nothing Then
            Call CaseLink_Confirm.CaseLink_Confirm
    End If

' Create Calendar Invite - Column U Only
    If Not Intersect(Target, Range("U:U")) Is Nothing Then
        If InStr(ActiveCell.Value, "Create Calendar Invite") > 0 Then
            Call CalendarInvite.CalendarInvite
    End If

I want to run the 5 Call sections regardless if the other calls fail.

Example:
1) When a selection change happens do:

' Hide Row - Column AQ with "Hide Row"
    If Not Intersect(Target, Range("AQ:AQ")) Is Nothing And InStr(ActiveCell.Value, "Hide Row") > 0 Then
            Call HideRow.HideRow
    End If

THEN regardless of the above do this:

' CaseLink - Confirm - Column T
    If Not Intersect(Target, Range("T:T")) Is Nothing Then
            Call CaseLink_Pre.CaseLink_Pre
    End If

THEN regardless of the above do this:

' CaseLink - Check - Column AJ
    If Not Intersect(Target, Range("AJ:AJ")) Is Nothing Then
            Call CaseLink_Check.CaseLink_Check
    End If

THEN regardless of the above do this:

' CaseLink - Confirm - Column AC
    If Not Intersect(Target, Range("AC:AC")) Is Nothing Then
            Call CaseLink_Confirm.CaseLink_Confirm
    End If

THEN regardless of the above do this:

' Create Calendar Invite - Column U Only
    If Not Intersect(Target, Range("U:U")) Is Nothing Then
        If InStr(ActiveCell.Value, "Create Calendar Invite") > 0 Then
            Call CalendarInvite.CalendarInvite
    End If

I thought what I had initially would work, but for some reason, when I had this at the bottom :

' CaseLink - Confirm - Column T
    If Not Intersect(Target, Range("T:T")) Is Nothing Then
            Call CaseLink_Pre.CaseLink_Pre
    End If

The code would not work as it seems like it was Exiting the Sub somewhere.

I just want all the 5 parts to run regardless of one another

Hopefully that makes sense

 

[StephenCrump]

The code would not work as it seems like it was Exiting the Sub somewhere.

I just want all the 5 parts to run regardless of one another

What makes you think these code lines are not running? Put in a breakpoint at the start of the Sub, and step through the code, and you should find that all these snippets run?

But this next snippet, for example, will call CaseLink_Pre.CaseLink_Pre only if the selection is a single cell in column T.

' CaseLink - Confirm - Column T
    If Not Intersect(Target, Range("T:T")) Is Nothing Then
        Call CaseLink_Pre.CaseLink_Pre
    End If
End Sub

Similar applies to the other snippets, but with different columns. Therefore regardless of which cell(s) are selected, the Sub will call a maximum of one other Sub.

As I said before:

Given your thread heading is "Run Multiple Calls every Worksheet_SelectionChange", it looks like you wanted to do something different?

Perhaps you could explain. What cell(s) are you selecting? What is happening? What do you want to happen?

A total guess, but perhaps you want your Subs to do something in columns T, AJ, AC etc based on Target.Row, i.e. in the same row as the Selection?