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