Help programming new chart into a new worksheet

A

aj

How do i set the chart location to create a new sheet called
"RiskMatrix" the first time the create chart button is pressed and then

have it replace the old "RiskMatrix" chart everytime it is pressed
again. Here is my Code


Private Sub CommandButton1_Click()

Sheets("Report").Activate

Dim WS As Worksheet
Dim Cht As Chart
Dim Rng As Range
Dim iRow As Long

Set WS = ActiveSheet
Set Cht = Charts.Add
Cht.ChartType = xlXYScatter

Set Rng = WS.Range(WS.Range("A2"), WS.Range("A2").End(xlDown))


For iRow = 2 To 1 + Rng.Rows.Count
Cht.SeriesCollection.NewSeries
With Cht.SeriesCollection(iRow - 1)
.XValues = "='" & WS.name & "'!R" & iRow & "C4"
.Values = "='" & WS.name & "'!R" & iRow & "C3"
.name = "='" & WS.name & "'!R" & iRow & "C1"
.ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=True, ShowCategoryName:=False,
ShowValue:=False, _
ShowPercentage:=False, ShowBubbleSize:=False
End With

Next

With Cht
.HasTitle = True
.ChartTitle.Text = "risk"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Impact"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Text = "Probability"
.HasAxis(xlCategory, xlPrimary) = False
.HasAxis(xlValue, xlPrimary) = False
.HasLegend = False
End With
With Cht.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With Cht.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top