Update Pivot Table/Chart based on selection for a list box

[cinstanl]

I currently have a workbook that has a form. The form is a listbox and combo box, (I will be removing the combo box). When the user launches the form, they will be selected all, one, two, three ... etc of different distributors. Once their selection is made a pivot should be updated. The pivot already exist, it just needs to be updated based upon their selection from the list box. The code that I currently have has a few issues.

1. It totally ignores the selection and displays what is currently set up on it.

2. It is creating a new chart instead of updating the already existing chart.

I know that the creation of the new chart is because of the coding. I'm not sure on what to change the coding to, to update what is currently in the chart.

I need help as soon as possible. I am on a contracted job and only have a few weeks left, with a ton of other things that I am creating for this dashboard left to do.

The entire code that I am using is listed below. I will be removing the portion of the code that refers to the combo box, the portion that I am having problems with start at "Private Sub CommandButton3_Click()" and goes to the end of the coding. Also, the attachment only has a few distributors, there are over 50, but the file would not attach because of the size so I had to delete some distributors.

Any help would be appreciated.


Dim i As Integer

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = True
Next i
End If

If CheckBox1.Value = False Then
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = False
Next i
End If

End Sub

Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
For i = 0 To ListBox2.ListCount - 1
ListBox2.Selected(i) = True
Next i
End If

If CheckBox2.Value = False Then
For i = 0 To ListBox2.ListCount - 1
ListBox2.Selected(i) = False
Next i
End If

End Sub



Private Sub CommandButton1_Click()

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then ListBox2.AddItem ListBox1.List(i)
Next i

For i = Me.ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(i) = True Then
Me.ListBox1.RemoveItem i
End If
Next i

End Sub


Private Sub CommandButton2_Click()

For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i) = True Then ListBox1.AddItem ListBox2.List(i)
Next i

For i = ListBox2.ListCount - 1 To 0 Step -1
If ListBox2.Selected(i) = True Then
ListBox2.RemoveItem i
End If
Next i

End Sub

Private Sub OptionButton1_Click()
ListBox1.MultiSelect = 0
ListBox2.MultiSelect = 0

End Sub

Private Sub OptionButton2_Click()
ListBox1.MultiSelect = 1
ListBox2.MultiSelect = 1

End Sub

Private Sub OptionButton3_Click()
ListBox1.MultiSelect = 2
ListBox2.MultiSelect = 2

End Sub

Private Sub UserForm_Initialize()
Dim myList As Collection
Dim myRange As Range
Dim ws As Worksheet
Dim myVal As Variant

With ComboBox1
.AddItem "Year to Date vs previous Year to Date"
.AddItem "Last 52 Weeks vs previous 52 weeks"
.AddItem "Last 13 weeks vS 13 weeks a year ago"
.AddItem "Last 13 weeks vS previous 13 weeks"
End With

Set ws = ThisWorkbook.Sheets("Locations")
Set myRange = ws.Range("k2", ws.Range("k2").End(xlDown))
Set myList = New Collection

On Error Resume Next
For Each myCell In myRange.Cells
myList.Add myCell.Value, CStr(myCell.Value)
Next myCell
On Error GoTo 0

For Each myVal In myList
Me.ListBox1.AddItem myVal
Next myVal


OptionButton2.Value = True

End Sub

Private Sub ListBox2_Change()
Dim i As Integer, cnt As Integer

With Me.ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) Then cnt = cnt + 1
Next
End With

Sheets("Product Group by Distributor ").Range("R8").Value = cnt

End Sub


Private Sub CommandButton3_Click()
MakeChart
Unload Distributor
End Sub



Sub MakeChart() 'code in question
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim gs As String

'Sheet1 = ComboBox1.Value

Application.ScreenUpdating = True
'First selected
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
Set rng1 = Columns(1).Find(ListBox1.List(i))
Exit For
End If
Next
'Last selected
For i = ListBox1.ListCount - 1 To 1 Step -1
If ListBox1.Selected(i) Then
Set rng2 = Columns(1).Find(ListBox1.List(i))
Exit For
End If
Next

Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Range("Sheet1!$A$3:$D$15"), PlotBy _
:=xlColumns
.SeriesCollection(1).Name = "=""LY"""
.SeriesCollection(2).Name = "=""CY"""
' .Location Where:=xlLocationAsObject, Name:=Sheet1
End With


Application.CommandBars("Chart").Visible = False
' Range("A5").Select
Application.ScreenUpdating = True

End Sub

[dflak]

As for the chart. Remove the MakeChart Subroutine. If you want to make the chart dynamic, then "overlay" the pivot table with named dynamic ranges. Here are a couple of articles that may help:
http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
http://www.utteraccess.com/wiki/inde...namic_Charting

[cinstanl]

Just in case anyone ever runs across this and needs it. I am going to post what I did to answer my own question. When reading the code, keep in mind that I had to also include a count code to count the number of locations associated with the distributors chosen. So if you need this code, make sure you comment that section out. Here is my entire code from the form that I used.

' Global and Public Variables
Dim i As Integer
Dim FilterDistributors() As String

'Option Explicit

Private Sub btnOK_Click()

Application.ScreenUpdating = False

' Declare variables:
Dim rng As Range
Dim index As Integer
Dim totalLocations As Integer
totalLocations = 0

' Check to see if at least one distributor is selected:
If ListBox2.ListCount = 0 Then

MsgBox "Please select at least one distributor!", vbCritical, "Error"

Else

' Filter out the pivot table based on the selections.

' 1) Find out the size remaining in ListBox1, and assign that to the size of the array.
ReDim FilterDistributors(ListBox1.ListCount)

' 2) Fill up the values in the array with the ones remaining in the List Box1.
For index = 0 To ListBox1.ListCount - 1

FilterDistributors(index) = ListBox1.List(index)

Next

' 3) Filter out the pivot table on PGbDPivot to only the values selected:
FilterChartOnDistributors ("Chart 1")
FilterChartOnDistributors ("Chart 2")
FilterChartOnDistributors ("Chart 3")
FilterChartOnDistributors ("Chart 4")
FilterChartOnDistributors ("Chart 5")
FilterChartOnDistributors ("Chart 6")
FilterChartOnDistributors ("Chart 7")
FilterChartOnDistributors ("Chart 8")
FilterChartOnDistributors ("Chart 9")
FilterChartOnDistributors ("Chart 10")
FilterChartOnDistributors ("Chart 11")
FilterChartOnDistributors ("Chart 12")
FilterChartOnDistributors ("Chart 13")
FilterChartOnDistributors ("Chart 14")
FilterChartOnDistributors ("Chart 15")
FilterChartOnDistributors ("Chart 16")
FilterChartOnDistributors ("Chart 17")
FilterChartOnDistributors ("Chart 18")

' 4) Update Number of Locations:
For index = 0 To ListBox2.ListCount - 1

' Find the range for the current distributor:
wsDataAll.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=ListBox2.List(index)
Set rng = Range(wsDataAll.Cells.Find(ListBox2.List(index), LookAt:=xlWhole).Offset(0, 14), wsDataAll.Cells.Find(ListBox2.List(index), LookAt:=xlWhole).Offset(0, 14).End(xlDown))

totalLocations = totalLocations + CountUnique(rng)

Next

' 5) Write the results:
wsProductGroupByDistributor.Range("R8").Value = totalLocations

' 6) Clear the filter from the table:
wsDataAll.ListObjects("Table1").Range.AutoFilter Field:=1

End If

' Go back to main worksheet
wsProductGroupByDistributor.Activate

Unload Distributor

End Sub
Sub FilterChartOnDistributors(NameOfChart As String)

Dim index As Integer

' First make sure all of the current filters are reset:
wsPGbDPivot.ChartObjects(NameOfChart).Activate
ActiveChart.PivotLayout.PivotTable.PivotFields("DISTRIBUTOR").ClearAllFilters

' Then, go through the distributors not selected and make them not visible (or filtered out):
For index = 0 To UBound(FilterDistributors) - 1

ActiveChart.PivotLayout.PivotTable.PivotFields("DISTRIBUTOR").PivotItems(FilterDistributors(index)).Visible = False

Next

End Sub
Public Function CountUnique(rng As Range) As Integer
Dim dict As Dictionary
Dim cell As Range
Set dict = New Dictionary
For Each cell In rng.Cells
If Not dict.Exists(cell.Value) Then
dict.Add cell.Value, 0
End If
Next
CountUnique = dict.Count
End Function
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = True
Next i
End If

If CheckBox1.Value = False Then
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = False
Next i
End If

End Sub

Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
For i = 0 To ListBox2.ListCount - 1
ListBox2.Selected(i) = True
Next i
End If

If CheckBox2.Value = False Then
For i = 0 To ListBox2.ListCount - 1
ListBox2.Selected(i) = False
Next i
End If

End Sub



Private Sub CommandButton1_Click()

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then ListBox2.AddItem ListBox1.List(i)
Next i

For i = Me.ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(i) = True Then
Me.ListBox1.RemoveItem i
End If
Next i

End Sub


Private Sub CommandButton2_Click()

For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i) = True Then ListBox1.AddItem ListBox2.List(i)
Next i

For i = ListBox2.ListCount - 1 To 0 Step -1
If ListBox2.Selected(i) = True Then
ListBox2.RemoveItem i
End If
Next i

End Sub

Private Sub OptionButton1_Click()
ListBox1.MultiSelect = 0
ListBox2.MultiSelect = 0

End Sub

Private Sub OptionButton2_Click()
ListBox1.MultiSelect = 1
ListBox2.MultiSelect = 1

End Sub

Private Sub OptionButton3_Click()
ListBox1.MultiSelect = 2
ListBox2.MultiSelect = 2

End Sub

Private Sub UserForm_Initialize()
Dim myList As Collection
Dim myRange As Range
Dim ws As Worksheet
Dim myVal As Variant

'With ComboBox1
' .AddItem "Year to Date vs previous Year to Date"
' .AddItem "Last 52 Weeks vs previous 52 weeks"
' .AddItem "Last 13 weeks vS 13 weeks a year ago"
' .AddItem "Last 13 weeks vS previous 13 weeks"
' End With

Set ws = ThisWorkbook.Sheets("Locations")
Set myRange = ws.Range("k2", ws.Range("k2").End(xlDown))
Set myList = New Collection

On Error Resume Next
For Each myCell In myRange.Cells
myList.Add myCell.Value, CStr(myCell.Value)
Next myCell
On Error GoTo 0

For Each myVal In myList
Me.ListBox1.AddItem myVal
Next myVal


OptionButton2.Value = True

End Sub