none
macro changing pivot filter based on cell value RRS feed

  • Question

  • Hi all, 

    I am really hoping someone is able to help med with following:

    I am creating a excel pivot report. One of my pivot report filter is quite big, thus I want enable the user of the report to write a number in a cell, which then will change the specific filter to that number. However after a long time of writing the macro, trial and error, and error searching I still get the same "invalid procedure or call argument" error. In a perfect world, the user should be able to type in the number on a dashboard/front page, which then changes the pivot filter on some hidden data sheets; however, for now I cant event make the macro work when it is types in the same sheet. 

    Following my macro. Any help regarding this is much appreciated. All the best:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'This line stops the worksheet updating on every change, it only updates when cell
    'H6 or H7 is touched
    If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub

    'Set the Variables to be used
    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As Double

    'Here you amend to suit your data
    Set pt = Worksheets("Data1").PivotTables("PivotTable10")
    Set Field = pt.PivotFields("[Produkter].[EanID].[EanID]")
    NewCat = Worksheets("Data1").Range("H6").Value


    'This updates and refreshes the PIVOT table

    ActiveSheet.PivotTables("PivotTable10").PivotFields("[Produkter].[EanID].[EanID]").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable10").PivotFields("[Produkter].[EanID].[EanID]").PivotFilters.Add Type:=xlValueEquals, Value1:=ActiveSheet.Range("H6").Value

    Thursday, January 12, 2017 11:09 PM

All replies

  • Hi MoellerAnd,

    This forum mainly focus on Office client related question and feedback. Since your issue is more related to developing issues involving Excel, I would move this thread into the following dedicated MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.

    Best regards,
     Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Friday, January 13, 2017 1:59 AM
  • Try below.

    If your field is in row/column/report then use xlCaptionEquals. ValueEquals applicable for Data field.

    Any macro should be first tested as normal sub.. then after testing you can put in event procedure of Excel.

    Any unspecified range/object may refer to either activesheet or the sheet module where the code resides. It is better to alsways qualify with Me when using event module.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
        'This line stops the worksheet updating on every change, it only updates when cell
         'H6 or H7 is touched
         
         If Intersect(Target, Me.Range("H6:H7")) Is Nothing Then Exit Sub
        
         'Set the Variables to be used
         Dim pt As PivotTable
         Dim pf As PivotField
         Dim NewCat As Double
        
         'Here you amend to suit your data
         Set pt = Me.PivotTables("PivotTable10")
         Set pf = pt.PivotFields("[Produkter].[EanID].[EanID]")
         NewCat = Target.Value
        
         'This updates and refreshes the PIVOT table
        
         pf.ClearAllFilters
         pf.PivotFilters.Add Type:=xlCaptionEquals, Value1:=NewCat
    
    
    End Sub
    
     
    
    


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Friday, January 13, 2017 5:11 AM
    Answerer
  • Hi Yuki Sun, 

    Sorry for the inconvenience, and was not aware. I will do so!
    Thank you

     
    Friday, January 13, 2017 6:35 AM
  • Hi Asadulla Javed, 

    Thank you so much for your input and amendment. 
    I sure do follow you inputs; however when I try your scripts, I still get the same error: 
    "Run time error 5
    Invalid procedure call or argument" 

    When debugging it occurs in the very last line of "

        pf.PivotFilters.Add Type:=xlCaptionEquals, Value1:=NewCat"
    Do you have any other suggestions? 

    Thank you so much for your help, I sure do appreciate it!
    Friday, January 13, 2017 6:40 AM
  • Hi all, 

    I am really hoping someone is able to help med with following:

    I am creating a excel pivot report. One of my pivot report filter is quite big, thus I want enable the user of the report to write a number in a cell, which then will change the specific filter to that number. However after a long time of writing the macro, trial and error, and error searching I still get the same "invalid procedure or call argument" error. In a perfect world, the user should be able to type in the number on a dashboard/front page, which then changes the pivot filter on some hidden data sheets; however, for now I cant event make the macro work when it is types in the same sheet. 

    Following my macro. Any help regarding this is much appreciated. All the best:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'This line stops the worksheet updating on every change, it only updates when cell
    'H6 or H7 is touched
    If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub

    'Set the Variables to be used
    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As Double

    'Here you amend to suit your data
    Set pt = Worksheets("Data1").PivotTables("PivotTable10")
    Set Field = pt.PivotFields("[Produkter].[EanID].[EanID]")
    NewCat = Worksheets("Data1").Range("H6").Value


    'This updates and refreshes the PIVOT table

    ActiveSheet.PivotTables("PivotTable10").PivotFields("[Produkter].[EanID].[EanID]").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable10").PivotFields("[Produkter].[EanID].[EanID]").PivotFilters.Add Type:=xlCaptionEquals, Value1:=ActiveSheet.Range("H6").Value

    Friday, January 13, 2017 6:44 AM
  • Have you checked your field name ? Two EANID is appearing.

    And try declaring NewCat as string

    As I do not have OLAP Pivot I could not test on exactly your scenario. Pls check and let me know.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Friday, January 13, 2017 8:23 AM
    Answerer
  • Thank you so much for your efforts! However unfortunately declaring NewCat as a string does not change the error message. 

    Regarding the EanID, I have already been running a macro in order to make sure the name was correct; thus I am certain the field name is the one stated. Further when converting out the last line i.e:

    'pf.PivotFilters.Add Type:=xlCaptionEquals, Value1:=NewCat

    the macro work as it clear the specific filers, but it can still not insert my "NewCat value".

    Friday, January 13, 2017 8:32 AM
  • What is EanID. Is it date , Text ?

    Is it in value field ?


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Friday, January 13, 2017 2:46 PM
    Answerer
  • The EanID is a number. 
    I am uncertain on the value field part; however, I have please see my screen below, in which I try to explain my intent more precisely 

    https://social.msdn.microsoft.com/Forums/getfile/988529 

     
    • Edited by MoellerAnd Friday, January 13, 2017 3:47 PM
    Friday, January 13, 2017 3:42 PM
  • In a perfect world, the user should be able to type in the number on a dashboard/front page, which then changes the pivot filter on some hidden data sheets

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    So why do you use the Worksheet_SelectionChange event? Why not the Worksheet_Change event?

    Andreas.

    Saturday, January 14, 2017 9:09 AM
  • Hi MoellerAnd,

    Your link is broken. I would suggest you share the screen and your excel file which we could make a test through OneDrive and share us link here.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 16, 2017 8:00 AM