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
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