+ Reply to Thread
Results 1 to 8 of 8

Add/Remove PivotField upon ListBox Selection

Hybrid View

  1. #1
    Registered User
    Join Date
    10-17-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    71

    Add/Remove PivotField upon ListBox Selection

    I wrote some code for a ListBox that aims at adding/removing corresponding PivotFields that are clicked within a ListBox. For example, when VOL 1 is selected in the ListBox, I want my PivotTable to add the VOL 1 PivotField item. If VOL 1 is deselected in the ListBox, I want my PivotTable to remove the VOL 1 PivotField item. VOL 1 is ListBox count item '0' and I will add additional ListBox items after I can get one to work. Here's my code:

    Private Sub ListBox1_Change()
    
    Dim i As Long
    
    With ListBox1
    For i = 0 To .ListCount - 1
    
    If .Selected(0) Then
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
            "PivotTable2").PivotFields("VOL 1"), "Count of VOL 1", xlCount
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of VOL 1")
            .Caption = "Sum of VOL 1"
            .Function = xlSum
            .NumberFormat = "$#,##0.00"
        End With
    
    Else
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of VOL 1").Orientation _
            = xlHidden
    
    End If
    Next i
    End With
    
    End Sub
    I get the following errors:
    Upon ListBox item selection: PivotTable field name already exists
    Upon ListBox item deselection: Unable to get the PivoFields property of the PivotTable class

    I'm sure this isn't a very difficult proplem to solve, but I have been unable to figure it out! Any assistance would be greatly appreciated!

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,324

    Re: Add/Remove PivotField upon ListBox Selection

    Why add it as a Count and then switch it to Sum?
    Dim PT as PivotTable
    Set PT = ActiveSheet.PivotTables("PivotTable2")
    If .Selected(0) Then
        PT.AddDataField PT.PivotFields("VOL 1"), "Sum of VOL 1", xlSum
        PT.PivotFields("Sum of VOL 1").NumberFormat = "$#,##0.00"
    Else
       PT.PivotFields("Sum of VOL 1").Orientation = xlHidden
    End If
    for example.
    End With

    End Sub
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    10-17-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: Add/Remove PivotField upon ListBox Selection

    No reason for having count and then switching to sum. The macro recorder just recorded the code like that.

    I used your code and am still getting some errors, but if the errors weren't there the function would perform fine as it looks like the code is adding and removing the data.

    Sub ListBox1_Change()
    
    Dim PT As PivotTable
    Set PT = ActiveSheet.PivotTables("PivotTable2")
    Dim i As Long
    
    With ListBox1
    For i = 0 To .ListCount - 1
    
    If .Selected(0) Then
    PT.AddDataField PT.PivotFields("VOL 1"), "Sum of VOL 1", xlSum
        PT.PivotFields("Sum of VOL 1").NumberFormat = "$#,##0.00"
        
    Else
    PT.PivotFields("Sum of VOL 1").Orientation = xlHidden
    
    End If
    Next i
    End With
    
    End Sub
    I get the following errors:
    Upon ListBox item selection: Application-defined or object defined error
    Upon ListBox item deselection: Unable to get the PivoFields property of the PivotTable class

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,324

    Re: Add/Remove PivotField upon ListBox Selection

    Can you post a workbook?

  5. #5
    Registered User
    Join Date
    10-17-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: Add/Remove PivotField upon ListBox Selection

    Hey romper, I really appreciate your help!! One thing I noticed with the listbox macro: as additional listbox items are selected, the code runs slower and slower (it takes longer and longer for the pivotfields to be displayed). This seems to only be happening with the items that require number format.

    Any ideas on what's slowing it down so much?

  6. #6
    Registered User
    Join Date
    10-17-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: Add/Remove PivotField upon ListBox Selection

    Hey romperstomper, I ended up using your code and adding the following line: On Error Resume Next. This fixed the issues with the debut window popping up. Everything is working fine now! Thanks again for your help.

  7. #7
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,324

    Re: Add/Remove PivotField upon ListBox Selection

    Probably better to add:
    PT.ManualUpdate = True
    ' all the code that manipulates the pivot goes here
    PT.Manualupdate = false

  8. #8
    Registered User
    Join Date
    10-17-2012
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: Add/Remove PivotField upon ListBox Selection

    Wow, that's perfect! You are the man!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Use a listbox to send multiple sleections to a single pivotfield in all pivot tables
    By roonstuff in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2014, 01:18 PM
  2. [SOLVED] OptionButton choice to populate ListBox based on the "Region" found in a PivotField.
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-26-2014, 04:30 PM
  3. remove selection from listbox
    By Darwish123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-12-2013, 05:05 AM
  4. Replies: 12
    Last Post: 08-28-2012, 07:09 AM
  5. Multi select Listbox Items selection based on other Listbox item selection.
    By srinivassathi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-20-2011, 05:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1