Menu
I am trying to create ErrorBars in a chart via Excel VBA, but I need the width to be 12PT, or to vary. Here is the code I'm using, but it doesn't look like it's working:
Set s = .SeriesCollection.NewSeries()
With s
.Name = "=GraphicSchedule!$" & getColumn(objList.ListColumns.Item("Activity").Range.Column) & "$" & sourceRow
.XValues = "=GraphicSchedule!$" & getColumn(objList.ListColumns.Item("DateMid").Range.Column) & "$" & sourceRow
.Values = "=GraphicSchedule!$" & getColumn(objList.ListColumns.Item("Loc1").Range.Column) & "$" & sourceRow
.HasErrorBars = True
.ErrorBar Direction:=xlX, Include:=xlErrorBarIncludeBoth, Type:=xlErrorBarTypeCustom, Amount:="=GraphicSchedule!$" & getColumn(objList.ListColumns.Item("BarLength").Range.Column) & "$" & sourceRow, MinusValues:="=GraphicSchedule!$" & getColumn(objList.ListColumns.Item("BarLength").Range.Column) & "$" & sourceRow
Set eB = .ErrorBars
With eB
With .Format.Line
.Visible = msoTrue
.Style = msoLineSingle
.Weight = 12
End With
.EndStyle = xlNoCap
End With
.HasDataLabels = True
Set dLabels = .DataLabels
With dLabels
.Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, "=GraphicSchedule!$" & getColumn(objList.ListColumns.Item("Activity").Range.Column) & "$" & sourceRow
.ShowRange = True
.ShowSeriesName = False
.ShowValue = False
End With
End With
I figured using the Weight property would work, but did I overlook something?
971
Click an error bar to select them all then use Command-1 to display the Format Error Bars dialog. In the left side click Line. On the right side choose Weights and Arrows. Choosing a different end style and size can make the error bar cap more noticeable.
Steps: First, select your chart and then the Chart Design ribbon will be visible in the ribbon bar. Later, click as follows from the ribbon: Add Chart Element > Error Bars > None.
You can make the error bar thicker by setting the elinewidth attribute in the call to errorbar (x,y,...) errorbar documentation. But the length of the error bar is your data: you can't change the length without changing the error that it represents. Show activity on this post.
Now, select the chart symbol from the Error Bars Options menu. Furthermore, under the Error Amount section, select Custom and click the Specify Value button next to the Custom option. The Custom Error Bars dialog will appear. For Positive Error Value, click on the range selector icon to select the range containing the positive eb ( D5:D8 ).
Is the errorbar width 12pt for you? It was only showing at 4 or so for me. Yes, it was. Same in 2016. The only way I can replicate is with On Error Resume Next and YourChartObject.ProtectFormatting = True which only resembles your trouble when the chart and error bars have already been created, and protection is set after.
Click the Chart Elements button. Click the arrow next to Error Bars and then click More Options… On the last tab of the Format Error Bars pane, under Error Amount, select Custom and click the Specify Value button. A small Custom Error Bars dialog box appears with two fields, each containing one array element like = {1}.
I think the problem is with .HasErrorBars = True which already creates an error bar automatically if one is not present, while the next line .ErrorBar creates another.
At this point you have two error bars, and .Format.Line.Weight = 12 in my case affected only the first automatically added one.
Try setting .HasErrorBars = False before using .ErrorBar and see if it makes a difference.
.HasErrorBars = False
.ErrorBar Direction:=xlX, Include:=xlErrorBarIncludeBoth, Type:=xlErrorBarTypeCustom, Amount:="=GraphicSchedule!$" & getColumn(objList.ListColumns.Item("BarLength").Range.Column) & "$" & sourceRow, MinusValues:="=GraphicSchedule!$" & getColumn(objList.ListColumns.Item("BarLength").Range.Column) & "$" & sourceRow
*Another thing to try is to toggle the .Format.Line.Visible after changes to refresh.
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
