VBA End if user selected cell is not within a series of named ranges

Question

I am having trouble with a bit of Select Case. I have my program working with named ranges. I want it to End if the select case is not in a series of named ranges. Here is my code that is running correctly for when the user selects a valid cell:

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("ActionDisplay")) Is Nothing Then
    Select Case Target.Column
        Case 5: Call Find_Action_RPI_Info
        Case 6: Call Find_Action_RPI_Info
        Case 7: Call Find_Action_RPI_Info
        Case 8: Call Find_Action_RPI_Info
        Case 9: Call Find_Action_RPI_Info
        Case 10: Call Find_Action_RPI_Info
        Case 11: Call Find_Action_RPI_Info
        Case 12: Call Find_Action_RPI_Info
        Case Else
    End Select
End If

If Not Intersect(Target, Range("ActionTotalDisplay")) Is Nothing Then
    Select Case Target.Column
        Case 13: Call Action_Total_RPI_Info
        Case Else
    End Select
End If

If Not Intersect(Target, Range("TotalDisplay")) Is Nothing Then
    Select Case Target.Column
        Case 5: Call CM_Action_Total_RPI_Info
        Case 6: Call CM_Action_Total_RPI_Info
        Case 7: Call CM_Action_Total_RPI_Info
        Case 8: Call CM_Action_Total_RPI_Info
        Case 9: Call CM_Action_Total_RPI_Info
        Case 10: Call CM_Action_Total_RPI_Info
        Case 11: Call CM_Action_Total_RPI_Info
        Case 12: Call CM_Action_Total_RPI_Info
        Case Else
    End Select
End If

If Not Intersect(Target, Range("GroupTotal")) Is Nothing Then
    Select Case Target.Column
        Case 13: Call GroupDisplay
    Case Else
    End Select
End If


If Not Intersect(Target, Range("PastDue")) Is Nothing Then
    Select Case Target.Column
        Case 7: Call PastDueDisplay
        Case 8: Exit Sub
        Case 9: Call PastDueDisplay
        Case 10: Exit Sub
    Case Else
    End Select
End If

If Not Intersect(Target, Range("PastDueTotal")) Is Nothing Then
    Select Case Target.Column
        Case 7: Call PastDueTotalDisplay
        Case 8: Exit Sub
        Case 9: Call PastDueTotalDisplay
        Case 10: Exit Sub
    Case Else
    End Select
End If


End Sub

So basically if its not in any of the above ranges I want the program to end. I am sure there are better ways to do what I am attempting but I am teaching myself all this so I am sure its not perfect.

score 2 · Accepted Answer · answered Feb 15 '13 at 19:55

2

You can try something like this (UNTESTED)

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("ActionDisplay")) Is Nothing Then
        Select Case Target.Column
            Case 5 To 12: Call Find_Action_RPI_Info
        End Select
    ElseIf Not Intersect(Target, Range("ActionTotalDisplay")) Is Nothing Then
        If Target.Column = 13 Then Call Action_Total_RPI_Info
    ElseIf Not Intersect(Target, Range("TotalDisplay")) Is Nothing Then
        Select Case Target.Column
            Case 5 To 12: Call CM_Action_Total_RPI_Info
        End Select
    ElseIf Not Intersect(Target, Range("GroupTotal")) Is Nothing Then
        If Target.Column = 13 Then Call GroupDisplay
    ElseIf Not Intersect(Target, Range("PastDue")) Is Nothing Then
        Select Case Target.Column
            Case 7, 9: Call PastDueDisplay
        End Select
    ElseIf Not Intersect(Target, Range("PastDueTotal")) Is Nothing Then
        Select Case Target.Column
            Case 7, 9: Call PastDueTotalDisplay
        End Select
    End If
End Sub

answered Feb 15 '13 at 19:55

Siddharth Rout

147,039
17
206
250

This is cleaner thank you. Somthing I noticed the portion: at the end for the pastDue case's, that portion now does not fucntion. It juest exits. Happen to know a reason? is it beacuse the columns at 7, 9? – user2044180 Feb 15 '13 at 21:14
If the column number os not 7 or 9 then it will not do anything and simply exit the sub – Siddharth Rout Feb 15 '13 at 21:17
It must be somthing in my error check call later in my code. Ill keep digging. I think its with my Selection Error check where i make sure the user selects one cell. 'IF selection.cells.count>1 then end' that may be forceing it out – user2044180 Feb 15 '13 at 21:25
I removed the error check and this solution is functioning corretly. I still would like to have a saftey net in place to exit the program if the user selects multiple cells. Right now if the user selects multiple cells it will run the program for the last cell in the selection. Can anyone give me a time on how to protect against that? – user2044180 Feb 15 '13 at 21:50
http://stackoverflow.com/questions/14771829/detecting-sheet-change-clearing-the-sheet-giving-an-overflow – Siddharth Rout Feb 15 '13 at 22:31
Thank you for your help Siddarth. I have fixed the issue using your suggestions it seems to be functioning correctly! – user2044180 Feb 15 '13 at 23:36

VBA End if user selected cell is not within a series of named ranges

1 Answers1