Link to home
Start Free TrialLog in
Avatar of Maliki Hassani
Maliki HassaniFlag for United States of America

asked on

Excel: VBA target = Combobox.value ?


I have a listbox that is chnages my charts.  I would like to add a target2 to show the chart title the complete value of the selection in the listbox.

Here is the code I am adding target2


Sub UpdateSummaryChartArray(ByVal lbItemCount As Integer)
Dim myRng As Variant
Dim Target As String
Dim Target2 As String  'Chart Title Name
Dim i As Integer
Dim mySeries As Series
Dim myRange As Range

   Target2 = ComboBox1.Value

    Sheets("Summary Dashboard").ChartObjects("Chart 124").Activate
    With ActiveChart
        i = .SeriesCollection.Count - 1
        For Each mySeries In .SeriesCollection
            If i > 0 Then
                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
        myRng = Application.WorksheetFunction.VLookup(Target, Range("Summary_Delta_Range"), Range("Summary_Delta_Range").Columns.Count, False)
        On Error GoTo 0

        If Err.Number = 0 And Not IsEmpty(myRng) Then
            Sheets("Summary Dashboard").ChartObjects("Chart 124").Activate
            With ActiveChart
                If i = 0 Then 'set x-axis labels only once
                    Set myRange = Range(myRng)
                    .SeriesCollection(1).XValues = "'Executive Rollup Data'!" & 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 = "'Executive Rollup Data'!" & Range(myRng).Address
                .SeriesCollection(i + 1).Name = Left(Target, InStr(Target, " ") - 1) 'name = initial word before a blank space of the combobox selection
                .HasTitle = True
                .ChartTitle.Text = Target2
                Selection.TickLabels.NumberFormat = Range("'Executive Rollup Data'!" & myRange.Cells(1, 1).Address).NumberFormat
            End With
            'do nothing
            Sheets("Summary Dashboard").ChartObjects("Chart 124").Activate
            With ActiveChart
                .HasTitle = True
                .ChartTitle.Text = "CAN'T FIND : " & Target
            End With
        End If
    Next i
End Sub

Open in new window

Avatar of dlmille
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Maliki Hassani


Great I will implement this!