VBA: Chart with Combobox

[sllaksvb]

Hi all,

I have been trying to pick up VBA over the past 2 weeks and am still relatievly new.

I am currently trying to create a chart with an ActiveX ComboBox that changes the source of the data when I select a different option in the ComboBox.
I have searching for a solution online, to no avail. I am not sure if I am heading in the right direction with the code.

It currently does what I need it to, but it completely removes the previous chart and places a new chart on top when i select a different option. However, I would like it to only change the data source, but maintain the same chart. My code is as follows:

Private Sub TimeCombo_Change()
    Select Case TimeCombo.Text
        
        Case "Past Week"
            Call PastWeekOption_Cl
        Case "Past Month"
            Call PastMonthOption_Cl
        Case "Past Quarter"
            Call PastQuarterOption_Cl
        Case "Year to Date"
            Call YearToDateOption_Cl
    
    End Select
End Sub

Sub PastWeekOption_Cl()
    Dim Top5_PWeek As Chart
    
        For Each Remove In Sheets("Sheet2").ChartObjects
            Remove.delete
        Next
    
        Set Top5_PWeek = Sheets("Sheet2").Shapes.AddChart(Left:=10, Width:=550, Top:=10, Height:=300).Chart
    
        With Top5_PWeek
            .SetSourceData Source:=Sheets("Sheets1").Range("A2:F9")
            .ChartType = xlLine
            .ChartArea.RoundedCorners = True
            .PlotArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 200)
            .HasTitle = True
            .ChartTitle.Text = "Top 5 Week"
            .Axes(xlValue).ScaleType = xlScaleLogarithmic
            .Axes(xlValue).LogBase = 10
            .Axes(xlValue).MinimumScale = 1000
            .ApplyLayout (3)
        End With
End Sub

Sub PastMonthOption_Cl()
    Dim Top5_PMonth As Chart
    
        For Each Remove In Sheets("Sheet2").ChartObjects
            Remove.delete
        Next
    
        Set Top5_PMonth = Sheets("Sheet2").Shapes.AddChart(Left:=10, Width:=550, Top:=10, Height:=300).Chart
    
        With Top5EarningsClient_PMonth
            .SetSourceData Source:=Sheets("Sheet1").Range("A2:F32")
            .ChartType = xlLine
            .ChartArea.RoundedCorners = True
            .PlotArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 200)
            .HasTitle = True
            .ChartTitle.Text = "Top 5 Month"
            .Axes(xlValue).ScaleType = xlScaleLogarithmic
            .Axes(xlValue).LogBase = 10
            .Axes(xlValue).MinimumScale = 1000
            .ApplyLayout (3)
        End With
End Sub

Any help would be appreciated! Thank you

[mdmackillop]

Please post a workbook with sample data and your combo. Go advanced/Manage Attachments

[sllaksvb]

Not sure if you understand what I am doing as I am unsure if I am doing it right.

Basically I am trying to create a chart with a combobox that displays different time frames when each option is selected, based on my data in sheet 1. However, my current code deletes all charts in sheet 2 and creates a new chart. I have been trying to figure out how I can keep the same chart and just update the data source.

[mdmackillop]

For each type as appropriate; delete the remove code.

Set Top5EarningsClient_Pweek = Sheets("Sheet2").ChartObjects(1).Chart

[sllaksvb]

Did as you instructed! Worked perfectly.
Deleted everything and left:

Sub PastWeekOption_Cl()
        Set Top5EarningsClient_PWeek = Sheets("Sheet2").ChartObjects(1).Chart
 
        With Top5EarningsClient_PWeek
            .SetSourceData Source:=Sheets("Client").Range("A2:F9")
        End With
End Sub

Thank you so much for your help! Really appreciate it.