none
Use combobox value in filter criteria RRS feed

  • Question

  • I have a combobox that has the name of different filters I have programmed, the list in the combobox is the names of those filters so when one is chosen it calls the vba code and executes the filter. I don't know how to write the code. The logic is as follows:

    if selection is remove all filters then execute remove all filters routine

    if selection is show all Net registrations then execute show net registrations routine

    if selection is sales in CIAPPR status then execute show CIAPPR sales routine

    This is the code I have but I don't know how to complete it:

    Sub DropDown310_Change()

    Dim mysht As Worksheet
    Dim myDropDown As Shape
    Dim myValSA As String

    Set mysht = ThisWorkbook.Worksheets("Pipeline")
    Set myDropDown = mysht.Shapes("Drop Down 310")
    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 PipelineShowCIAPPR
     Else
        DON'T KNOW WHAT TO PUT HERE


    End Sub


    MEC

    Saturday, July 16, 2016 7:16 PM

Answers

  • I am assuming that "Net Regs" and "CIAPPR" are the values that you want to filter and that it will always be the same column that is filtered. In the following code example you need to set the Field number that will be filtered (See the comment where you need to edit)

    Sub DropDown310_Change()

        Dim mysht As Worksheet
        Dim myDropDown As Shape
        Dim myValSA As String
       
        Set mysht = ThisWorkbook.Worksheets("Pipeline")
       
        'Following If / End If code is optional but best to ensure AutoFilter is on.
        If mysht.AutoFilterMode = False Then
            mysht.UsedRange.AutoFilter      'Turn on AutoFilter
        End If
       
        Set myDropDown = mysht.Shapes("Drop Down 310")
        myValSA = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value)
       
        With mysht
            Select Case myValSA
                Case "Remove Filters"
                    If .AutoFilterMode Then 'Test that AutoFilter is applied
                        If .FilterMode Then 'Test if any filters are set
                            .ShowAllData    'Turn off any set filters
                        End If
                    End If
                Case Else
                    If .FilterMode Then 'Test if any filters already set
                        .ShowAllData  'Cancel any existing set filters (Errors if no existing filters set and hence the if test)
                    End If
                    With .AutoFilter.Range   'Generic object for the filtered range
                        'In following line edit Field:=1 to required Field number to filter
                        .AutoFilter Field:=1, Criteria1:=myValSA
                    End With
            End Select
        End With

    End Sub


    Regards, OssieMac

    Sunday, July 17, 2016 3:47 AM