PDA

View Full Version : [SOLVED] VBA: Chart with Combobox



sllaksvb
08-02-2017, 12:23 PM
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
08-02-2017, 03:30 PM
Please post a workbook with sample data and your combo. Go advanced/Manage Attachments

sllaksvb
08-03-2017, 08:05 AM
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
08-03-2017, 09:35 AM
For each type as appropriate; delete the remove code.

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

sllaksvb
08-03-2017, 10:07 AM
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.