Results 1 to 3 of 3

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

Threaded View

  1. #1
    Registered User
    Join Date
    05-25-2016
    Location
    Erlanger, KY
    MS-Off Ver
    2010
    Posts
    81

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

    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. update table based on the selection in userform
    By melody10 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-15-2015, 11:25 AM
  2. Replies: 2
    Last Post: 12-10-2014, 12:42 AM
  3. Update Data selection for chart based off a referenced cell
    By mcini in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-29-2014, 05:02 PM
  4. Update formula in cells based on list selection
    By XLNB in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-05-2014, 03:39 PM
  5. Update formula in cells based on list selection
    By XLNB in forum Excel General
    Replies: 1
    Last Post: 01-05-2014, 03:17 PM
  6. Update a cell based on selection in dropdown list.
    By ladykickbox in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-26-2012, 07:45 AM
  7. Update List of Fixtures based on Selection
    By mccrimmon in forum Excel General
    Replies: 2
    Last Post: 06-10-2011, 10: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