VBA code to exit sub if combobox selection not found in data RRS feed

  • Question

  • I have a combobox that executes perfectly using the value selected from combobox. It searches the data for the value and runs through the routine. The problem I have is when it does not find the data it freezes on me and gives me an out of memory error. How can i incorporate code to give me a msg box that data is not found and exit sub?

    Here is my code: 

    Sub DropDown311_Change()

    'Filters Combobox
    Dim mysht As Worksheet
    Dim myDropDown As Shape
    Dim myValSA As String

    Set mysht = ThisWorkbook.Worksheets("Pipeline")
    Set myDropDown = mysht.Shapes("Drop Down 311")
    myValSA = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value)

    If myValSA = "Remove Filters" Then Call Pipeline_Remove_All_Filters
        If myValSA = "Net Regs" Then Call Pipeline_ShowNetRegs
        If myValSA = "CIAPPR" Then Call PipelineShowCIAPPRLoans
        If myValSA = "APPR" Then Call PipelineShowAPPROVEDLoans
        'End If
    Worksheets("Pipeline").Shapes("Drop Down 311").ControlFormat.Value = 1
    End Sub


    Thursday, July 21, 2016 1:07 AM


  • >>>How can i incorporate code to give me a msg box that data is not found and exit sub?

    According to your description, you could use the CASE statement instead of IF statement, you could refer to below code:
    Select Case myValSA
        Case "Remove Filters"
           Call Pipeline_Remove_All_Filters
        Case "Net Regs"
           Call Pipeline_ShowNetRegs
        Case "CIAPPR"
           Call PipelineShowCIAPPRLoans
        Case "APPR"
           Call PipelineShowAPPROVEDLoans
        Case Else
           MsgBox "That data is not found!"
           Exit Sub
    End Select
    Worksheets("Pipeline").Shapes("Drop Down 311").ControlFormat.Value = 1
    For more information, click here to refer about Select...Case Statement (Visual Basic)

    In addition please correct me if I have any misunderstandings on your question, you could provide more information about this issue, for example error code line, screenshot etc., that will help us reproduce and resolve it.

    Thanks for your understanding. 
    • Marked as answer by David_JunFeng Friday, July 29, 2016 7:03 AM
    Friday, July 22, 2016 5:21 AM