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...

    Code
    1. myfilter1 = "POOLED SPACE"
    2. myfilter2 = "6010"
    3. Range("PS_Usage").AutoFilter Field:=16, Criteria1:=myfilter1
    4. 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.