CODEWITHSUNDEEP
excelvba
M Kaye
M Kaye

Reputation: 183

Remove series from chart legend and set axis range in excel vba

I am using VBA and am trying to install a chart and am trying to make the y axis have years as the values. However a new series comes in to play called series 17 which appears in the legend. Is there anyway to delete this from the legend but not remove it as the x axis values. I have already tried "MyChartObj.Chart.SeriesCollection(17).Delete" but this removes it as y axis values.

Also does anyone know how to set the range of values on an axis. For example I would like the chart to go between 0 and 100 rather than 0 and 120 which is currently happening.

Here is my code:

Sub orange_weightings_chart()

Dim MyChtObj As ChartObject
Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
Dim Sht1Name As String
Dim Sht2Name As String

Set Sht2 = Worksheets("Orange Weightings")
Set Sht1 = Worksheets("Weightings Table")
Sht1Name = Sht1.Name
Sht2Name = Sht2.Name
Set MyChtObj = Sht1.ChartObjects.Add(100, 100, 500, 500)

Set a = Sht1.Range("A2", Sht1.Range("A2").End(xlToRight))
Set b = Sht1.Range("A3", Sht1.Range("A3").End(xlToRight))
Set c = Sht1.Range("A4", Sht1.Range("A4").End(xlToRight))
Set d = Sht1.Range("A5", Sht1.Range("A5").End(xlToRight))
Set e = Sht1.Range("A6", Sht1.Range("A6").End(xlToRight))
Set f = Sht1.Range("A7", Sht1.Range("A7").End(xlToRight))
Set g = Sht1.Range("A8", Sht1.Range("A8").End(xlToRight))
Set h = Sht1.Range("A9", Sht1.Range("A9").End(xlToRight))
Set i = Sht1.Range("A10", Sht1.Range("A10").End(xlToRight))
Set j = Sht1.Range("A11", Sht1.Range("A11").End(xlToRight))
Set k = Sht1.Range("A12", Sht1.Range("A12").End(xlToRight))
Set l = Sht1.Range("A13", Sht1.Range("A13").End(xlToRight))
Set m = Sht1.Range("A14", Sht1.Range("A14").End(xlToRight))
Set n = Sht1.Range("A15", Sht1.Range("A15").End(xlToRight))
Set o = Sht1.Range("A16", Sht1.Range("A16").End(xlToRight))
Set p = Sht1.Range("A17", Sht1.Range("A17").End(xlToRight))
Set InputData = Union(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p)

With MyChtObj.Chart
    .ChartType = xlBarStacked
    .SetSourceData InputData
    .PlotBy = xlRows
    .HasTitle = True
    .ChartTitle.Text = "Original Forecast Parameter Weightings"
    .HasLegend = True
    .SeriesCollection.NewSeries.XValues = Sht1.Range("B1", Sht1.Range("B1").End(xlToRight))
End With

With MyChtObj
    .Top = Sht2.Range("E15").Top
    .Left = Sht2.Range("E15").Left
    .Width = 1200
    .Height = 380
End With

End Sub

Thanks in advance,

Max

Upvotes: 2

Views: 5607

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

To remove Series 17 only from the legend (and not from the chart), and also to set the Chart axis from 0 to 100, use the code below:

With MyChtObj.Chart
    .Legend.LegendEntries(17).Delete  ' delete the legend only

    .Axes(xlValue).MinimumScale = 0 ' set minimum value
    .Axes(xlValue).MaximumScale = 100  ' set maximum value
End With

Upvotes: 1

Related Questions