none
How to save filtered criteria in a list box?

    Question

  • 

    Hello Developers, I have userform that does the filter based on value entered in

    textbox against labels equal, not equal, contains and do not

    conatins. Below is the look of user form. Labels Textbox fld1 TB2 TB3 TB4 TB5 Equal not equal contains usa do not contains NY Buttons FILTER DELETE Move Flag So once user enters usa against label contains in tb1 and NY

    against do not contains in tb2 and when they click button filter

    than only those rows with usa with NY excluded are shown.

    once rows are filtered than user select to either delete, move or

    flag those rows

    The below code is working fine. All i want is to save all the

    filter values supplied by the user in seperate sheet.

    so take above e.g where user entered usa and ny and once the rows

    are filtered accordingly than user choose to delete that data than

    seperate sheet shall save it like following Delete all rows having TB1 contains USA and TB2 do not contain

    NY I want to save above line in sheet only when user decides to either

    click on Delete, Move or Flag. and whenever user wants to run the

    same filter again than they can apply that filter by selecting

    filter rules already saved in seperate sheet by the user.

    Private Sub cmdFilter_Click() Dim lngCount As Long Dim I As Long Dim j As Long Dim lngCountj As Long Dim k As Long Dim arrStrings Dim lngLastRow As Long lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row If lngLastRow < lngCriteriaRow Then lngLastRow = lngCriteriaRow End If Range(Cells(lngCriteriaRow, 1), Cells(lngLastRow, 20)).ClearContents For I = 1 To 10 If Me.Controls("cbxField" & I) <> "" Then If Me.Controls("txtEqual" & I) <> "" Then lngCount = lngCount + 1 Cells(lngCriteriaRow - 1, lngCount) = Me.Controls("cbxField" & I) Cells(lngCriteriaRow, lngCount) = Me.Controls("txtEqual" & I) End If If Me.Controls("txtNotEqual" & I) <> "" Then lngCount = lngCount + 1 Cells(lngCriteriaRow - 1, lngCount) = Me.Controls("cbxField" & I) Cells(lngCriteriaRow, lngCount) = "<>" & Me.Controls("txtNotEqual" & I) End If If Me.Controls("txtCtn" & I) <> "" Then lngCount = lngCount + 1 Cells(lngCriteriaRow - 1, lngCount) = Me.Controls("cbxField" & I) If InStr(Me.Controls("txtCtn" & I), ",") > 0 Then If j > 0 Then MsgBox "You can specify multiple values for one field only!", vbCritical Exit Sub End If j = I lngCountj = lngCount ' Handle the rest at the end Else Cells(lngCriteriaRow, lngCount) = "*" & Me.Controls("txtCtn" & I) & "*" End If End If If Me.Controls("txtNotContain" & I) <> "" Then lngCount = lngCount + 1 Cells(lngCriteriaRow - 1, lngCount) = Me.Controls("cbxField" & I) Cells(lngCriteriaRow, lngCount) = "<>*" & Me.Controls("txtNotContain" & I) & "*" End If End If Next I If j > 0 Then arrStrings = Split(Me.Controls("txtCtn" & j), ",") Cells(lngCriteriaRow, lngCountj) = "*" & Trim(arrStrings(0)) & "*" For k = 1 To UBound(arrStrings) Range(Cells(lngCriteriaRow, 1), Cells(lngCriteriaRow, lngCount)).Copy _ Destination:=Range(Cells(lngCriteriaRow + k, 1), Cells(lngCriteriaRow + k, lngCount)) Cells(lngCriteriaRow + k, lngCountj) = "*" & Trim(arrStrings(k)) & "*" Next k k = k - 1 Else k = 0 End If If lngCount > 0 Then Range("A1").CurrentRegion.AdvancedFilter _ Action:=xlFilterInPlace, _ CriteriaRange:=Range(Cells(lngCriteriaRow - 1, 1), _ Cells(lngCriteriaRow + k, lngCount)) ElseIf ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Range(Cells(lngCriteriaRow - 1, 1), Cells(lngCriteriaRow, 1)) _ .EntireRow.ClearContents End Sub

    I am ok if the delete criteria and field name is saved in seperate sheet




    • Edited by zaveri cc Thursday, December 19, 2013 6:53 PM
    Thursday, December 19, 2013 6:50 PM

All replies

  • Hello Developers,

    I have userform that does the filter based on value entered in textbox against labels equal, not equal, contains and do not conatins.

    Below is the look of user form.

    Labels                          Textbox fld1        TB2                TB3             TB4              TB5

    Equal

    not equal

    contains                        usa

    do not contains                                        NY

                                                                                Buttons

                                                   FILTER     DELETE         Move          Flag 

    So once user enters usa against label contains  in tb1 and NY against do not contains and when they click button filter than only those rows with usa with NY excluded are shown .

    once rows are filtered than user select to either delete, move or flag those rows

    The below code is working fine. All i want is to create onother list box which will save all the filter values supplied by the user.

     so take above e.g where user entered usa and ny and once the rows are filtered accordingly than user choose to delete that data than list box shall save it like following

       Delete all rows having TB1 contains USA and TB2 do not contain NY

     and whenever user wants to run the same filter again than they can apply that filter by selecting filter rules already saved in list box by the user.

    

    

    Private Sub cmdFilter_Click() Dim lngCount As Long Dim I As Long Dim j As Long Dim lngCountj As Long Dim k As Long Dim arrStrings Dim lngLastRow As Long lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row If lngLastRow < lngCriteriaRow Then lngLastRow = lngCriteriaRow End If Range(Cells(lngCriteriaRow, 1), Cells(lngLastRow, 20)).ClearContents For I = 1 To 10 If Me.Controls("cbxField" & I) <> "" Then If Me.Controls("txtEqual" & I) <> "" Then lngCount = lngCount + 1 Cells(lngCriteriaRow - 1, lngCount) = Me.Controls("cbxField" & I) Cells(lngCriteriaRow, lngCount) = Me.Controls("txtEqual" & I) End If If Me.Controls("txtNotEqual" & I) <> "" Then lngCount = lngCount + 1 Cells(lngCriteriaRow - 1, lngCount) = Me.Controls("cbxField" & I) Cells(lngCriteriaRow, lngCount) = "<>" & Me.Controls("txtNotEqual" & I) End If If Me.Controls("txtCtn" & I) <> "" Then lngCount = lngCount + 1 Cells(lngCriteriaRow - 1, lngCount) = Me.Controls("cbxField" & I) If InStr(Me.Controls("txtCtn" & I), ",") > 0 Then If j > 0 Then MsgBox "You can specify multiple values for one field only!", vbCritical Exit Sub End If j = I lngCountj = lngCount ' Handle the rest at the end Else Cells(lngCriteriaRow, lngCount) = "*" & Me.Controls("txtCtn" & I) & "*" End If End If If Me.Controls("txtNotContain" & I) <> "" Then lngCount = lngCount + 1 Cells(lngCriteriaRow - 1, lngCount) = Me.Controls("cbxField" & I) Cells(lngCriteriaRow, lngCount) = "<>*" & Me.Controls("txtNotContain" & I) & "*" End If End If Next I If j > 0 Then arrStrings = Split(Me.Controls("txtCtn" & j), ",") Cells(lngCriteriaRow, lngCountj) = "*" & Trim(arrStrings(0)) & "*" For k = 1 To UBound(arrStrings) Range(Cells(lngCriteriaRow, 1), Cells(lngCriteriaRow, lngCount)).Copy _ Destination:=Range(Cells(lngCriteriaRow + k, 1), Cells(lngCriteriaRow + k, lngCount)) Cells(lngCriteriaRow + k, lngCountj) = "*" & Trim(arrStrings(k)) & "*" Next k k = k - 1 Else k = 0 End If If lngCount > 0 Then Range("A1").CurrentRegion.AdvancedFilter _ Action:=xlFilterInPlace, _ CriteriaRange:=Range(Cells(lngCriteriaRow - 1, 1), _ Cells(lngCriteriaRow + k, lngCount)) ElseIf ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Range(Cells(lngCriteriaRow - 1, 1), Cells(lngCriteriaRow, 1)) _ .EntireRow.ClearContents End Sub





    Thursday, December 05, 2013 4:47 PM
  • Hi,

    >>All i want is to create onother list box which will save all the filter values supplied by the user.
    than list box shall save it like following

    Delete all rows having TB1 contains USA and TB2 do not contain NY <<

    Do you want to save all the filter values supplied by the user such as the “TB1 contains USA and TB2 do not contain NY”, “TB1 contains USA” and “TB2 do not contain NY”, or “Delete all rows having TB1 contains USA and TB2 do not contain”?

    To add a list box to store the filter value and the operation, I think you could add text item into the list box in the button click event such as cmdFilter_Click or cmdDelete_Click. Before executing the AdvancedFilter event or Delete event you could organize all the filter criteria as a string and add it to the list box.

    ListBox1.AddItem " TB1 contains USA and TB2 do not contain NY "

    >>whenever user wants to run the same filter again than they can apply that filter by selecting filter rules already saved in list box by the user.<<

    When you want to use the item text of the list box to filter, you could add a DblClick event of the listBox to split the string as criteria and filter the data.

    Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox "aaa"
    End Sub

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 06, 2013 11:56 AM
    Moderator
  • I want to save Delete all rows having TB1 contains USA and TB2 do not contain. so once filter is applied and if user delete, move or flag that row than only filtered criteria shall be saved to the list box.

    I will try your suggestion and let you know.



    • Edited by zaveri cc Tuesday, December 10, 2013 9:00 PM
    Friday, December 06, 2013 2:49 PM
  • To add a list box to store the filter value and the operation, I think you could add text item into the list box in the button click event such as cmdFilter_Click or cmdDelete_Click. Before executing the AdvancedFilter event or Delete event you could organize all the filter criteria as a string and add it to the list box.

    ListBox1.AddItem " TB1 contains USA and TB2 do not contain NY "

    what code is going to capture the above listbox1.additem?

    I alreday have filter click and delete click which will filter the records and delete the records simultaneously so how can i add 2 more filter click and delete click code.

     I don't need to save just what user filters. I want to save only when user decides to delete, move or flag the filter rows.

    Can you please provide some code where user delete some row using the filter criteria than those delete criteria shall be saved . lets say user first filter column A that contains USA and column C than do not cotain nj. once the filter is applied than user decides to delete those rows. Now list box shall capture this delete criteria. List box shall save- delete rows where column A cotains USA and column C do not contain NJ.




    • Edited by zaveri cc Tuesday, December 10, 2013 9:16 PM
    Tuesday, December 10, 2013 9:10 PM
  • Hi,

    >>what code is going to capture the above listbox1.additem?

    Now list box shall capture this delete criteria.<<

    I don't think you should capture this delete criteria. Since you said you will "delete,move or flag that row than only filtered criteria shall be saved to the list box." You could just execute "listbox1.additem" in you delete click, move click or flage click event.

    1.store the delete criteria into a String

    2.execute delete operation

    3.use listbox1.additem to add the delete criteria String

    >>I alreday have filter click and delete click which will filter the records and delete the records simultaneously so how can i add 2 more filter click and delete click code.<<

    You don't need to add 2 more filter click and delete click event. I just said you could add the listbox1.additem in your existed events.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 13, 2013 11:53 AM
    Moderator
  • Can you provide the code example when user clicks the delete button and show what will be saved in the list box.

    I tried adding listbox1.addItem under code to flag the data and run the flag code but list box did not saved anything

    • Edited by zaveri cc Saturday, December 14, 2013 6:27 PM
    Friday, December 13, 2013 3:55 PM
  • Well then it sounds like you answered your own question. This is an ACCESS forum and not an EXCEL forum but in ACCESS we save data or criteria in a table field and in EXCEL you do it in a worksheet cell. Same thing. I would create a worksheet to hold your input criteria and then you can reference that worksheet cell as a filtering criteria when the form opens if that's what you want to do.
    Thursday, December 19, 2013 6:18 PM
  • But filter criteria like contains, do not contanis, equal and not equal are in labels and against that labels user put value in textbox. so how shall i caputre criteria and value for that criteria

    can you give some code example?

    I do not know what will be saved in the sheet? just text or complete sql delete qry?

    a code to just save will really help.


    • Edited by zaveri cc Thursday, December 19, 2013 8:27 PM
    Thursday, December 19, 2013 6:55 PM
  • 

    The below code is working fine. All i want is to save all the

    filter values supplied by the user in seperate sheet.

    I am ok if the delete criteria and field name is saved in seperate sheet

    I am not really sure if the following is what you are after. If not, then perhaps some more explanation.

    The Criteria is actually a Defined name (See the worksheets's defined names after running the code.)

    Therefore you should be able to copy the range defined by Criteria to another location immediately after running the code like the following example between the asterisk lines.

        If lngCount > 0 Then
            Range("A1").CurrentRegion.AdvancedFilter _
                Action:=xlFilterInPlace, _
                criteriarange:=Range(Cells(lngCriteriaRow - 1, 1), _
                    Cells(lngCriteriaRow + k, lngCount))
            '*******************************************************************
            'Copy Criteria to another worksheet
            Range("Criteria").Copy Destination:=Worksheets("Sheet2").Range("A1")
            '********************************************************************
                   
        ElseIf ActiveSheet.FilterMode Then
            ActiveSheet.ShowAllData
        End If
        Range(Cells(lngCriteriaRow - 1, 1), Cells(lngCriteriaRow, 1)) _
            .EntireRow.ClearContents


    Regards, OssieMac

    Friday, December 20, 2013 1:44 AM
  • Hi Mac,

    Your code is capturing the column name and value entered against the label (equal, not equal, contians and do not contains). I have choose field "NAME" from my textbox fld1. Using vba code my 5 textbox(TB1 to TB5) reads column names from sheet 1 range A1 TO A15

    First let me expalain what i am trying to do.

    
    Below is the look of user form.
     
    Labels      Textbox fld1    TB2       TB3    TB4     TB5
     
    Equal
     
    not equal
     
    contains      MA
     
    do not contains              
     
    
                         Buttons
     
          FILTER     DELETE         Move          Flag 

    Now as per above e.g, i am entering ma against label contains and click button filter. Data in sheet1 gets filtered with name contains "ma". Till now i do not want to save anything in sheet2.

    Now i want to delete this filtered data. This time my sheet 2 shall save like following

    sheet2

    Row1      Column Name            value          criteria1      criteria2      crt3              action

    Row2          Name                    ma            contains                                            DELETE

    So

    1. column name shall be saved in row2, column1
    2. value entered shall be saved in row 2, column2 under value. current save will be ma
    3. Criteria 1 to 3 shall save equal, not equal, contains and do not contains. I am not going to user more than 3 criteria. Current save will be contians.
    4. action shall capture delete.

    Once action take place than only things shall be saved in sheet2.

    • Edited by zaveri cc Friday, December 20, 2013 2:50 PM
    Friday, December 20, 2013 2:30 PM