Displaying 2 charts in an Excel sheet VBA

[xboon_95]

Hi, I'm trying to display 2 charts in an excel sheet using VBA with 2 command buttons. Whenever i click on button 1, it will display the chart that i wanted BUT an empty white box will also appear. Same goes for button 2. I tried using the following code:

With myChart1
On Error Resume Next
ActiveSheet.ChartObjects.Delete
On Error GoTo 0

However, it only allows me to display the new graph and it will delete the previous graph that i wanted. So how do i delete the empty white box? I tried searching for 'deleting blank chart in excel vba' but to no avail. Please help

 

[xboon_95]

These are my codes for the 2 commandbuttons :


Private Sub CommandButton1_Click() 'First button

Dim myChart As ChartObject
Dim StartVal As Long, endVal As Long

StartVal = Sheet1.Range("E1").Value
endVal = Sheet1.Range("E2").Value


Set myChart = ActiveSheet.ChartObjects.Add(100, 30, 300, 200)


On Error Resume Next
ActiveSheet.ChartObjects.Delete
On Error GoTo 0


Charts.Add
With ActiveChart
.ChartType = xlLine
.SetSourceData Source:=Sheets("Sheet1").Range("C" & StartVal & ":C" & endVal)
.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.Axes(xlValue).HasTitle = True
ActiveChart.Axes(xlValue).AxisTitle.Text = "Pressure"
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "Pressure against Time"
ActiveChart.Axes(xlCategory).HasTitle = True
ActiveChart.Axes(xlCategory).AxisTitle.Text = "Timing/s"
With ActiveChart.Parent
.top = Range("N12").top
.left = Range("N5").left
.width = Range("C1:J18").width
.height = Range("C1:J18").height

End With
End With
End Sub


Private Sub CommandButton2_Click() 'Second button

Cells.Find(What:="00:00:01", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
Dim A
A = ActiveCell.Row + 1

Application.ActiveSheet.Range("F1").Value = A


Cells.Find(What:="00:09:10", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate


Dim B
B = ActiveCell.Row

Application.ActiveSheet.Range("F2").Value = B



Dim myChart1 As ChartObject
Dim StartVal As Long, endVal As Long


StartVal = Sheet1.Range("F1").Value
endVal = Sheet1.Range("F2").Value




Set myChart1 = ActiveSheet.ChartObjects.Add(60, 30, 300, 200)


On Error Resume Next
ActiveSheet.ChartObjects.Delete
On Error GoTo 0


Charts.Add
With ActiveChart
.ChartType = xlLine
.SetSourceData Source:=Sheets("Sheet1").Range("C" & StartVal & ":C" & endVal)
.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.Axes(xlValue).HasTitle = True
ActiveChart.Axes(xlValue).AxisTitle.Text = "Pressure"
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "Pressure against Time"
ActiveChart.Axes(xlCategory).HasTitle = True
ActiveChart.Axes(xlCategory).AxisTitle.Text = "Timing/s"
With ActiveChart.Parent
.top = Range("E12").top
.left = Range("E5").left
.width = Range("C1:J18").width
.height = Range("C1:J18").height
ActiveWindow.ScrollRow = 1

End With
End With
End Sub

 

[xboon_95]

The other problem is that the charts will stack with one another. Each time i click on a command button, a new graph will appear with the white charting box with no series and it will just stack.
link : View image: mpsip1