Special Offer: Buy 1 course, get 2nd free! Buy the 'Managing Office 365 Identities & Requirements' course w/ Accelerated TestPrep, and automatically receive the 'Enabling Office 365 Services' course FREE!
Sub UpdateGenericChartArray(ByVal lbItemCount As Integer, dataRange As String, dataSheet As String, chartSheet As String, chartID As String, obUsed As Boolean) Dim myRng As Variant Dim Target As String Dim i As Integer Dim mySeries As Series Dim myRange As Range Dim foundData As Boolean foundData = False Sheets(chartSheet).ChartObjects(chartID).Activate With ActiveChart i = .SeriesCollection.Count - 1 For Each mySeries In .SeriesCollection If i > 0 Then mySeries.Delete i = i - 1 End If Next mySeries End With For i = 0 To lbItemCount - 1 'for each item in the listbox that was selected Target = lbArray(i) 'keep the code as similar as possible to original combobox routine On Error Resume Next Err.Clear If Not obUsed Then obMainChart = 2 'go with last 8 weeks as default, to keep all your charts working and to chart things that don't have the option button for date range, they will go with last 3 months myRng = Application.WorksheetFunction.VLookup(Target, Range(dataRange), Range(dataRange).Columns.Count - obMainChart, False) On Error GoTo 0 If Err.Number = 0 And Not IsEmpty(myRng) Then Sheets(chartSheet).ChartObjects(chartID).Activate With ActiveChart If i = 0 Then 'set x-axis labels only once Set myRange = Range(myRng) .SeriesCollection(1).XValues = "'" & dataSheet & "'!" & Range(Cells(5, myRange.Cells(1, 1).Column), Cells(5, myRange.Cells(1, myRange.Columns.Count).Column)).Address End If If i > 0 Then .SeriesCollection.NewSeries ' don't create the new series till past the first one .SeriesCollection(i + 1).Values = "'" & dataSheet & "'!" & Range(myRng).Address .SeriesCollection(i + 1).Name = Left(Target, InStr(Target & " ", " ") - 1) 'add a space after, just in case it doesn't have a first one -> name = initial word before a blank space of the combobox selection End With foundData = True Else 'do nothing foundData = False End If Next i 'now format the resulting chart - do this outside the loop as the last one is the only one displayed, anyway - more efficient... If foundData Then With ActiveChart .HasTitle = True .ChartTitle.Text = Target .Axes(xlValue).Select Selection.TickLabels.NumberFormat = Range("'" & dataSheet & "'!" & myRange.Cells(1, 1).Address).NumberFormat End With Else With ActiveChart .HasTitle = True .ChartTitle.Text = "CAN'T FIND : " & Target End With End If End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.