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
Bookmarks