I cannot find a VBA property to set the Marker Border of a single point in a chart (recording bug?)

[TomCon]

If you select a single point on a chart series to format it, you can then, within the UI dialog "Format Data Point", specifically and separately set a format for (a) "Line" (which sets format properties for the line segment connecting that point to the previous point in the chart series), or (b) for the "border of the marker".

However, the recorder records exactly the same thing, for whichever of the above 2 [different, non-identical] actions you take in the UI dialog (format the Line or format the Marker Border). This is what is recorded for each case (identical):
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSysDot
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 2
End With

If you replay the above macro, what it actually does is format the LINE's properties (the line segment of the series leading to the selected point) and DOES NOT format the border of the marker with the DashStyle and Visible properties.

Does anybody know if there is a property I can use in VBA that will actually do the same thing as is done in the UI when you select a single chart point and then in the "Format Data Point" dialog go to Marker->Border and set a property for the Marker Border?

Thanks for your help!

Thank you.

 

[Zot]

You meant line thickness?

Weight = xlThin

Got xlMedium and also xlThick

 

[TomCon]

Sorry apparently was not exactly clear.

The problem is not with the thickness; it is that the Weight is being applied to the Line, and the not as the border of the marker's thickness.

What is the property to apply a new Weight for Marker Border thickness?

It is apparently Not "Line" but yet "Line" is not is what the macro recorder records.

 

[TomCon]

Sorry apparently was not exactly clear.

The problem is not with the thickness; it is that the Weight is being applied to the Line, and the not as the border of the marker's thickness.

What is the property to apply a new Weight for Marker Border thickness?

It is apparently Not "Line" but yet "Line" is not is what the macro recorder records.

 

[Michael M]

I'm not certain here, but don't you need to modify Forecolor AND Backcolor
Something like

.SeriesCollection(c).MarkerForegroundColorIndex = 5
.SeriesCollection(c).MarkerBackgroundColorIndex = 42

 

[Zot]

Are you talking about the marker line properties? If so, then it is something like this.

How do I set the "marker line style" with VBA

Hi, In Excel 2007 I can right-click on a chart series (XYScatter) and click "Marker Line Style" on the left. I can then set the marker's line Width, Compound Type, Dash type, Cap type, Join type,

answers.microsoft.com

 

[TomCon]

I'm not certain here, but don't you need to modify Forecolor AND Backcolor
Something like

.SeriesCollection(c).MarkerForegroundColorIndex = 5
.SeriesCollection(c).MarkerBackgroundColorIndex = 42

MichaelM - thank you this seems to be getting closer. For the Point object, i find properties for MarkerForegroundColor, MarkerBackgroundColor, MarkerStyle, and MarkerSize.

But I do not find properties for the "Marker Border Line". Looking for properties like the Line Type (msoLineDash, etc), Line Weight (usually in points like 1.0, 1.25, etc), and Line Color. NOT the line of the chart series that connects the point, but the Line Type of the border of the marker. These properties are specifically settable in the user interface in the dialog "Format Data Point".

Does anybody know if there are properties that exist for this?

Thanks!

 

[TomCon]

THREAD SUMMARY.

This thread is hard to follow but i will try to do a grand summary.

First of all thanks to all for the comments and advice. They did contribute to helping me with this issue although none "fully solves" it.

THIS AREA OF VBA IS A MESS. It does not really work properly, and i cannot find properties to make it fully work. Although due to advice in this thread have cobbled together something that "almost" does what i need so it will have to be "good enough". Properties exist for VBA objects that are not even listed in the object browser for that object.

The problem:

• In the UI, using the "Format Data point" dialog, you can select alot of formatting options that apply to that single point's marker to make it different from the other points in the series, including border type (line, dashes, etc), border color, border thickness.
• There are not sufficient VBA properties to duplicate this behavior in VBA.
• What the recorder records when you perform the actions in the dialog, when replayed, change not only the formats of the point that you selected, but also applies the formats you selected for the border of the marker, to the line segment of the chart that lead to the point. In other words, "border marker" and "line segment" are mingled in VBA but are separate in the UI.

What I wanted to accomplish in VBA.
As series (Scatterplot) has no markers (line only) and is line type msoSysDot. I want the FINAL point (only) to have a marker, that the marker match the series line in Width and Color, but be msoLineSolid.

Partial solutions.

• The link posted by Zot to an Excel 2007 article identifies this exact issue. (So i guess Microsoft has not fixed this issue for 14 years!). It suggests an approach of accepting the "defect" in the macro that it changes the line segment also, and then after that runs, try to change the line segment back. This works for some but not all of the properties. Specifically, there is not a property (that I can find) that will allow you to separately control the DashStyle property for the line segment and for the marker border. They have to match (and i do not want them to; I want the marker border to be msoLineSolid while the chart line segment needs to be msoLineSysDot).
• The properties mentioned by Michael M are helpful as they do affect only the marker, not the line segment. But, there are not enough of them to cover everything that the UI allows you to do. The property provided, Point.Format.Line.Weight, does not correspond to the "Width" property in the UI; some widths you can select in the UI cannot be duplicated in VBA and the number you do set in your code is "mysteriously rounded" and you have to play around to find a setting that works "somewhat closely to what you want". And this property is applied by VBA to both the marker border and to the line segment. The property Point.MarkerForegroundColorIndex does not use the same color scheme that the UI offers, and so again you have to play around and guess at numbers and settle for one that is "more or less ok". Microsoftdocumentation is incoorrect for this value, stating that it has only 2 values, Automatic and None, but in fact it is a series of colors that seem to work from 0 to approximately 60.

=======APPENDIX TO THIS POST========
In case anybody wants to work on this issue any further.

Macro to run to create a chart to experiment on.
Sub Create_Example()
'
' Macro19 Macro
'

'
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
ActiveCell.FormulaR1C1 = "2"
Range("A3").Select
ActiveCell.FormulaR1C1 = "3"
Range("A4").Select
ActiveCell.FormulaR1C1 = "4"
Range("B1").Select
ActiveCell.FormulaR1C1 = "2"
Range("B2").Select
ActiveCell.FormulaR1C1 = "4"
Range("B3").Select
ActiveCell.FormulaR1C1 = "3"
Range("B4").Select
ActiveCell.FormulaR1C1 = "6"
Range("A1:B4").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$4")
ActiveChart.FullSeriesCollection(1).Select
Selection.MarkerStyle = -4142
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
.DashStyle = msoLineSysDot
.Weight = 2
End With
End Sub

Chart created while recording in the UI. Select the last point in the series. In the "Format Data Point"dialog, for the Marker: set color, border width, border line type.

Macro recorded while creating the above chart in the UI.
Sub Macro_As_Recorded()
'
' Macro23 Macro
'

' MAKE SURE CHART IS SELECTED BEFORE RUNNIN THIS RECORDED MACRO.


' ActiveSheet.ChartObjects("Chart 11").Activate
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).Points(4).Select
With Selection
.MarkerStyle = -4142
.MarkerSize = 5
End With
Selection.MarkerStyle = 2
Selection.MarkerSize = 10
Selection.Format.Fill.Visible = msoFalse
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent2
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.25
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 3
End With
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
End With
End Sub

The chart as it will appear after replaying the above recorded macro on the original, clean example case.

Macro I now use, "best i can do" with some comments.

Sub Attempt_Fix_Line()

'This can be run on a clean example
'MAKE SURE CHART IS SELECTED BEFORE RUNNING THIS MACRO

ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).Points(4).Select

Selection.MarkerStyle = xlMarkerStyleCircle
Selection.MarkerSize = 10
Selection.Format.Fill.Visible = msoFalse

'This does not allow all of the same colors as the UI.
Selection.MarkerForegroundColorIndex = 0 '0 is black

'Weight is not the same as "width" in the UI. Have to guess to find right Weight
'Not all width's in the UI can be duplicated with Weight, as it is rounded (strangely)
'This changes both chart line segment and border width. Cannot control them separately.
'Fortunately i can live with "2" for both.
Selection.Format.Line.Weight = 2

'Cannot find a way to control dash style. It now seems to default to Solid so ok
'But in other cases was getting Dashed lines for the marker border that i did not want
' and could not fix.

End Sub

 

[RoryA]

As far as I know, there are no exposed properties for setting marker line weights in VBA.

 

[Zot]

VBA on chart is full of mystery (at least for me) ?. When you write code in raw manner like (this is just example to try to explain what I meant which may not be true in this case)
ChartObject.Chart.SeriesCollection.NewSeries

Then you put dot, there is no Intellisense to guide you. However, if you declare the whole thing like
Dim cht as Object
Dim s As Series
Set s = cht.Chart.SeriesCollection.NewSeries

Then you type s. , Intellisense will work. Something like that.

I'm not sure is this is what you were looking for but I managed to set scatter chart marker line weight like this (just to draw line)

Sub AddLineSeries(cht As ChartObject, sName As String, xvals, yvals, SeriesColor As Long)

Dim s As Series

Set s = cht.Chart.SeriesCollection.NewSeries

With s
    .ChartType = xlXYScatterLinesNoMarkers
    .Name = sName
    .Values = yvals
    .XValues = xvals
'    .MarkerBackgroundColor = xlNone
'    .MarkerForegroundColor = SeriesColor
    .MarkerStyle = xlMarkerStyleNone
    With .Border
        .Weight = 3
        .Color = SeriesColor
    End With
End With

End Sub

IF you want to alter any specific point or marker, then you have to select the point by its number.

Like you said Line and Border go together. Another link on border

Border.LineStyle (Excel VBA)

Information about the procedure LineStyle of class Border

codevba.com