Posts by Romela

    Greetings

    I have working code for a pivot table (pt) but want to filter the 2nd pt further from value at 1st pt.


    Code
    1. Dim LResult As String
    2. PSselect = myfilterTypePS
    3. LResult = Left(myfilterTypePS, 5)
    4. With PvtTblRental
    5. .PivotFields("Facility Number").ClearLabelFilters
    6. .PivotFields("Facility Number").PivotFilters.Add Type:=xlCaptionEquals,
    7. '.PivotFields("RS_Usage_UIC").PivotFilters.Add Type:=xlCaptionEquals, Value1:=LResult Value1:=myfilterFacNum
    8. End With


    The 3rd line under the "With PvtTblRental" does not execute properly - for a good reason as follows.


    The 1st pt contains "61050 Admin", and the 2nd pt "61050 Admin (other text)"


    I cannot use the 1st pt string to filter the 2nd pt because the strings are not equal. So my following question is:


    Can I use something like ".PivotFields("RS_Usage_UIC") = Left(myfilterTypePS, 5)" which would just consider the first 5 characters - and basically making the strings equal.


    Then I can filter the 2nd pt with "61050".

    I thought about an example but since the code is working correctly for me I decided to just show the code.


    Since ".PivotFields("Facility Number").PivotFilters.Add Type:=xlCaptionEquals, Value1:=myfilterFacNum" is the line of interest; and more specifically, "Value1:=myfilterFacNum" is the filter being applied to the pivot table.


    I am asking how do I insert a "Value2:=myfilter2" using the "PivotFilters" property so the pivot table will now be filtered on Two Criteria.


    Thanks for your response.

    I have some sample code below that is working fine for me. Currently has one filter (on Facility Number) and I want to add another filter.



    I am having a problem with this line.


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


    Which filters the pt as desired. But how do I add a "second filter" - seems I cannot get the syntax correct. thanks for your suggestions.

    I am searching for the best method to tie these forms together so the user sees them all, and can switch between them.


    However, now I want to begin to "spatially" aligned these forms for the users interaction.


    So what is the best method to do that? Should I create "one large form" as the background, and ties all the other forms to it?


    Thanks,

    Greetings


    Let me add one more item; some minor code from my combobox.


    Code
    1. Private Sub cbo_SelectUsage_Change() 'select pooled space usage type (CU Catcode)
    2. 'store "PS Main Usage Catcode" at worksheet cell
    3. Sheets("AdminCtrls").Range("B15").Value = cbo_SelectUsage.Text
    4. End Sub

    Notice how I stored the "combo box change event" to cell B15 (1st graphic, top left.)


    I want to be able to select the "Row_ID" just to the left of the users-choice, and store it also in cell B14.


    Any advise is greatly appreciated as I am really not getting the results desired, thanks.

    Greetings


    I have a pivot table called "pt_UsagePS" as shown in the 1st graphic (upper right).


    1228213-pasted-from-clipboard-png


    Basically, If the user selects "74062 - FST FD/SNK BAR (cellT8)" from the combo box to follow, I want to store the "Row_ID (cell S8) for later use.

    As you can see at cell B15, I store the results of the user's selection.


    1228214-pasted-from-clipboard-png


    As you see, the combobox is displaying the four-items correctly from the "filtered pivot table"


    This is how I accomplished it in vba (with your prior 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.

    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.

    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.

    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.