BeforeDoubleClick inc 5 named ranges

[L7B]

Hi

I can get this to work with 2 named ranges but as I add the others it stops working.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Call Create_Shape_CLOSE_KPI_DEFINITION

    If Not Intersect(Target, Range("Employee_Performance_Plans")) Is Nothing Then
Call Show_EmployeePerformancePlans
End If
 
    If Intersect(Target, Range("Employee_Engagement")) Is Nothing Then Exit Sub
Call Show_ENGAGEMENT
Else
  
  If Intersect(Target, Range("Performance_Reviews_Completion")) Is Nothing Then Exit Sub
Call Show_PerformanceReviewsCompletion
Else
    If Intersect(Target, Range("Employee_Turnover")) Is Nothing Then Exit Sub
Call Show_EmployeeTurnover
Else
    If Intersect(Target, Range("Absenteeism_")) Is Nothing Then Exit Sub
Call Show_Absenteeism
End If
End Sub

Thanks for the help

 

[Gary McMaster]

Difficult to test with all the named ranges and calls to other procedures that are not posted, but the "Exit Sub" statements are preventing it from ever evaluating the lines near the bottom. As soon as the second test evaluates to "Nothing" it exits the sub and it never has a chance to test the rest of the conditions/intersections. I assume that is what you mean by you can only make it work with 2 named ranges.

Not sure what's going on with the all the Ifs, End Ifs, Else etc. Looks like you need some serious restructuring and/or ElseIfs in there.

Maybe try the sample below and/or provide a better description of exactly what you're trying to do.

Gary

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Call Create_Shape_CLOSE_KPI_DEFINITION

'Could possibly "Union" all 5 named ranges and exit if there is no intersection with target instead of individual Exit Subs

If Not Application.Intersect(Target, Range("Employee_Performance_Plans")) Is Nothing Then
    Call Show_EmployeePerformancePlans
    Exit Sub 'Maybe not needed if named ranges don't overlap
End If

If Not Application.Intersect(Target, Range("Employee_Engagement")) Is Nothing Then
    Call Show_ENGAGEMENT
    Exit Sub 'Maybe not needed if named ranges don't overlap
End If

If Not Application.Intersect(Target, Range("Performance_Reviews_Completion")) Is Nothing Then
    Call Show_PerformanceReviewsCompletion
    Exit Sub 'Maybe not needed if named ranges don't overlap
End If

If Not Application.Intersect(Target, Range("Employee_Turnover")) Is Nothing Then
    Call Show_EmployeeTurnover
    Exit Sub 'Maybe not needed if named ranges don't overlap
End If

If Not Application.Intersect(Target, Range("Absenteeism_")) Is Nothing Then
    Call Show_Absenteeism
    Exit Sub 'Maybe not needed if named ranges don't overlap
End If

End Sub

 

[L7B]

Hi Gary

Thanks ever so much works a dream.

Lee

 

[L7B]

Hi Gary

How do I union the 5 name ranges.

Thanks
Lee

 

[Gary McMaster]

Hi Lee,

Here's a sample. I believe Union will accept up to 30 ranges as arguments.

Gary

Public Sub Test()

'Union 4 ranges named "E","G","I" & "Row1" change range names to suit

Dim oUnion As Range

Set oUnion = Union(ActiveSheet.Range("E"), ActiveSheet.Range("G"), ActiveSheet.Range("I"), ActiveSheet.Range("Row1"))

oUnion.Interior.ColorIndex = 3

End Sub