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 ?

Experts!

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

Thanks

 
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
                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
        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
                .Axes(xlValue).Select
                Selection.TickLabels.NumberFormat = Range("'Executive Rollup Data'!" & myRange.Cells(1, 1).Address).NumberFormat
            End With
        Else
            '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

ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
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

ASKER

Great I will implement this!