Results 1 to 11 of 11

Add/Remove Multiple Calculated Fields with Listbox (ISSUE)

Threaded View

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

    Add/Remove Multiple Calculated Fields with Listbox (ISSUE)

    I'm reluctant that someone will be able to figure this out, but here it goes:

    When clicking my listbox items (MultiSelect LisbBox), it populates various calcuated fields in my pivot table. I'm trying to figure out how to Remove and Add my calculated field items when they are selected in the listbox. The code below runs without errors, and it populates the pivot field with the desired calculated fields. However, when I DESELECT the 2nd listbox item, it does not remove the calculated field. If I deselect the 1st listbox item, then ALL of the calculated fields will be removed from the pivottable. How do I modify the code below so that each calculated field is removed individually when it is deselected from the listbox.

    Many thanks in advance if someone can solve this!

    Private Sub ListBox8_Change()
    '$CHANGE from Standard/T1 Cost
    
    Application.ScreenUpdating = False
    Dim i As Long
    ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
    With ListBox8
    For i = 0 To .ListCount - 1
    
    On Error Resume Next
    
     Dim pt As PivotTable
     Dim pf As PivotField
     Dim pfNew As PivotField
     Dim strSource As String
     Dim strFormula As String
    
    
    If .Selected(0) Then
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("STANDCHG$ 12"), Sheets("SCodes").Range("AF3").Value, xlSum
        With ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("SCodes").Range("AF3").Value)
            .NumberFormat = "$#,####0.0000"
        End With
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("T1CHG$ 12"), Sheets("SCodes").Range("AG3").Value, xlSum
        With ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("SCodes").Range("AG3").Value)
            .NumberFormat = "$#,####0.0000"
        End With
    Else
    
    Set pt = ActiveSheet.PivotTables(1)
    For Each pf In pt.CalculatedFields
        strSource = pf.SourceName
        strFormula = pf.Formula
        pf.Delete
        Set pfNew = pt.CalculatedFields.Add(strSource, strFormula)
     Next pf
    End If
    
    'Next Month
    
    If .Selected(1) Then
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("STANDCHG$ 11"), Sheets("SCodes").Range("AF4").Value, xlSum
        With ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("SCodes").Range("AF4").Value)
            .NumberFormat = "$#,####0.0000"
        End With
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("T1CHG$ 11"), Sheets("SCodes").Range("AG4").Value, xlSum
        With ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("SCodes").Range("AG4").Value)
            .NumberFormat = "$#,####0.0000"
        End With
    Else
    
    'ISSUE HERE WITH NOT DISPLAYING A DIFFERENT MONTH OF DATA (Sheets("SCodes").Range("AF4") AND ("AG4")
    
     For Each pf In pt.CalculatedFields
        strSource = pf.SourceName
        strFormula = pf.Formula
        pf.Delete
        Set pfNew = pt.CalculatedFields.Add(strSource, strFormula)
     Next pf
    End If
    
    Next i
    End With
    ActiveSheet.PivotTables("PivotTable1").ManualUpdate = False
    End Sub
    It looks like different things happen when I move around the position of this line of code:
    Set pt = ActiveSheet.PivotTables(1)
    but I'm not sure if this is the issue.
    Last edited by Platinum3x; 12-05-2014 at 02:16 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Issue with Calculated Fields with pivot tables
    By msantucci in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 09-18-2014, 10:31 AM
  2. [SOLVED] Populate Multiple Fields from Mutiple Column Listbox
    By sgwilliams in forum Excel General
    Replies: 6
    Last Post: 02-07-2011, 02:47 PM
  3. Calculated Fields Based on Running Total Fields?
    By Kruncher in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-12-2006, 01:25 PM
  4. [SOLVED] How do I remove (form) check-boxes from multiple excel fields?
    By ATSANAC in forum Excel General
    Replies: 3
    Last Post: 05-11-2006, 01:55 PM

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