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!
Bookmarks