1

I'm trying to automate plotting of a box and whiskers chart. The code below compiles and runs but the error bars do not appear in the chart.

Dim ws As Worksheet
Dim datarange As Range
Dim chtChart As Chart
Dim objChrt As ChartObject

Set ws = Sheets("sheet1")
Set datarange = ws.Range("F8:G10")

Set chtChart = Charts.add

With chtChart

  chtChart.ChartType = xlColumnStacked
  chtChart.SetSourceData Source:=datarange, PlotBy:=xlColumns

  With .Axes(xlCategory, xlPrimary)
        .CategoryNames = ws.Range("A2:A13")
        .TickLabels.Font.Bold = True
  End With
      .SeriesCollection(1).Format.Fill.Visible = msoFalse
      .SeriesCollection(2).Format.Fill.Visible = msoTrue


    Dim Sec1 As Series
    Set Sec1 = .SeriesCollection.NewSeries
    .SeriesCollection(3).HasErrorBars = True
    .SeriesCollection(3).ErrorBars.EndStyle = xlCap
    .SeriesCollection(3).ErrorBars.Format.Line.Visible = msoTrue
    .SeriesCollection(3).ErrorBars.Format.Line.ForeColor.RGB = RGB(0, 0, 0)
    .SeriesCollection(3).ErrorBars.Format.Line.Transparency = 0
    .SeriesCollection(3).ErrorBars.Format.Line.Weight = 1.5
    .SeriesCollection(3).ErrorBar Direction:=xlX, Include:=xlErrorBarIncludeBoth, Type:=xlErrorBarTypeCustom, Amount:="=CHART!F12:G12", MinusValues:="=CHART!F12:G12"
GarENt sIrUS
  • 11
  • 2

1 Answers1

0

Solved.

    .SeriesCollection(3).values= <data range for whisker length>

type variant, data values required to plot whiskers IMPORTANT: this overwrites the series collection values (seriescollection(3) in this case) values can be replaced after plotting whiskers

GarENt sIrUS
  • 11
  • 2