Fill Form's Combobox With Filtered Range

  • This was a response from Kennett Hobson in 2007. I used the code code.

    I am receiving the run-time error message '1004': AutoFilter methods of Range class failed.

    Any ideas? thanks for your assistance, Romela.

  • Let me provide some additional information, see the graphic below.

    The goal: after the two criteria are set; "POOLED SPACE" and "6010", I want the combobox to be populated with the items in the last column - where the user will select the appropriate record.

    Here is the revise code borrowed from Kennett Hobson in 2007.

  • Possibly...

        myfilter1 = "POOLED SPACE"
        myfilter2 = "6010"
        Range("PS_Usage").AutoFilter Field:=16, Criteria1:=myfilter1
        Range("PS_Usage").AutoFilter Field:=17, Criteria2:=myfilter2

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Greetings

    So let's continue same subject just a little twist. I have taken a different approach as follows with a pivot table called "pt_UsagePS"..

    Before I begin there is one more image from the form with the Combobox.

    As you see, the combobox is displaying the four-items correctly from the "filtered pivot table". When the user selects an item, I store the result in B15 (1st graphic) for later use.

    In addition, from the pivot table; and for whatever item is chosen, I want to store "Row_ID" as show in the 1st column of the pivot table.

    This is how I accomplished it in vba (with help of course).

    Set PvtTbl = Worksheets("AdminCtrls").PivotTables("pt_UsagePS") 'Set creates an Object

    With PvtTbl

    .PivotFields("Facility Number").ClearLabelFilters

    .PivotFields("Facility Number").PivotFilters.Add Type:=xlCaptionEquals, Value1:=myfilter2

    End With

    Finally, How do I select the "Row_ID in vba so I can store it? Thanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!