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

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

i having problem bit of select case. have programme working named ranges. want end if select case not in series of named ranges. here code running correctly when user selects valid cell:

private sub worksheet_selectionchange(byval target range) if not intersect(target, range("actiondisplay")) nil select case target.column case 5: phone call find_action_rpi_info case 6: phone call find_action_rpi_info case 7: phone call find_action_rpi_info case 8: phone call find_action_rpi_info case 9: phone call find_action_rpi_info case 10: phone call find_action_rpi_info case 11: phone call find_action_rpi_info case 12: phone call find_action_rpi_info case else end select end if if not intersect(target, range("actiontotaldisplay")) nil select case target.column case 13: phone call action_total_rpi_info case else end select end if if not intersect(target, range("totaldisplay")) nil select case target.column case 5: phone call cm_action_total_rpi_info case 6: phone call cm_action_total_rpi_info case 7: phone call cm_action_total_rpi_info case 8: phone call cm_action_total_rpi_info case 9: phone call cm_action_total_rpi_info case 10: phone call cm_action_total_rpi_info case 11: phone call cm_action_total_rpi_info case 12: phone call cm_action_total_rpi_info case else end select end if if not intersect(target, range("grouptotal")) nil select case target.column case 13: phone call groupdisplay case else end select end if if not intersect(target, range("pastdue")) nil select case target.column case 7: phone call pastduedisplay case 8: exit sub case 9: phone call pastduedisplay case 10: exit sub case else end select end if if not intersect(target, range("pastduetotal")) nil select case target.column case 7: phone call pastduetotaldisplay case 8: exit sub case 9: phone call pastduetotaldisplay case 10: exit sub case else end select end if end sub

so if not in of above ranges want programme end. sure there improve ways attempting teaching myself sure not perfect.

you can seek (untested)

private sub worksheet_selectionchange(byval target range) if not intersect(target, range("actiondisplay")) nil select case target.column case 5 12: phone call find_action_rpi_info end select elseif not intersect(target, range("actiontotaldisplay")) nil if target.column = 13 phone call action_total_rpi_info elseif not intersect(target, range("totaldisplay")) nil select case target.column case 5 12: phone call cm_action_total_rpi_info end select elseif not intersect(target, range("grouptotal")) nil if target.column = 13 phone call groupdisplay elseif not intersect(target, range("pastdue")) nil select case target.column case 7, 9: phone call pastduedisplay end select elseif not intersect(target, range("pastduetotal")) nil select case target.column case 7, 9: phone call pastduetotaldisplay end select end if end sub

excel-vba named-ranges select-case