Excel VBA xlMarkerStyle not plotting -
the following excel vba module designed change color of various line segments based on condition. works great except not plot individual line markers. problem apparently exists in each of xlmarkerstylecircle, marketsize, markerbackgroundcolor, , markerforegroundcolor lines. i'm not sure if problem related improper object naming or improper sequencing of object references. or suggestions appreciated. likewise, if sees more efficient way of coding same objective, please feel free share.
thanks kindly...
cheers, john
sub tropical_cyclone_track_format() activesheet set r = .range("e24:e31") = 1 .shapes("chart 3").chart.seriescollection(1).points.count - 1 if r(i) = "1" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markerstyle = xlmarkerstylecircle if r(i) = "1" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markersize = 2 if r(i) = "1" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markerbackgroundcolor = rgb(0, 0, 0) if r(i) = "1" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markerforegroundcolor = rgb(0, 0, 0) if r(i) = "1" .shapes("chart 3").chart.seriescollection(1).points(i + 1).border.linestyle = xlcontinuous if r(i) = "1" .shapes("chart 3").chart.seriescollection(1).points(i + 1).border.color = rgb(255, 255, 64) if r(i) = "1" .shapes("chart 3").chart.seriescollection(1).points(i + 1).format.line.transparency = 0 if r(i) = "1" .shapes("chart 3").chart.seriescollection(1).points(i + 1).format.line.weight = 1 if r(i) = "2" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markerstyle = xlmarkerstylecircle if r(i) = "2" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markersize = 2 if r(i) = "2" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markerbackgroundcolor = rgb(0, 0, 0) if r(i) = "2" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markerforegroundcolor = rgb(0, 0, 0) if r(i) = "2" .shapes("chart 3").chart.seriescollection(1).points(i + 1).border.linestyle = xlcontinuous if r(i) = "2" .shapes("chart 3").chart.seriescollection(1).points(i + 1).border.color = rgb(255, 153, 16) if r(i) = "2" .shapes("chart 3").chart.seriescollection(1).points(i + 1).format.line.transparency = 0 if r(i) = "2" .shapes("chart 3").chart.seriescollection(1).points(i + 1).format.line.weight = 1 if r(i) = "3" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markerstyle = xlmarkerstylecircle if r(i) = "3" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markersize = 2 if r(i) = "3" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markerbackgroundcolor = rgb(0, 0, 0) if r(i) = "3" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markerforegroundcolor = rgb(0, 0, 0) if r(i) = "3" .shapes("chart 3").chart.seriescollection(1).points(i + 1).border.linestyle = xlcontinuous if r(i) = "3" .shapes("chart 3").chart.seriescollection(1).points(i + 1).border.color = rgb(255, 3, 0) if r(i) = "3" .shapes("chart 3").chart.seriescollection(1).points(i + 1).format.line.transparency = 0 if r(i) = "3" .shapes("chart 3").chart.seriescollection(1).points(i + 1).format.line.weight = 1 if r(i) = "4" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markerstyle = xlmarkerstylecircle if r(i) = "4" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markersize = 2 if r(i) = "4" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markerbackgroundcolor = rgb(0, 0, 0) if r(i) = "4" .shapes("chart 3").chart.seriescollection(1).points(i + 1).markerforegroundcolor = rgb(0, 0, 0) if r(i) = "4" .shapes("chart 3").chart.seriescollection(1).points(i + 1).border.linestyle = xlcontinuous if r(i) = "4" .shapes("chart 3").chart.seriescollection(1).points(i + 1).border.color = rgb(80, 0, 0) if r(i) = "4" .shapes("chart 3").chart.seriescollection(1).points(i + 1).format.line.transparency = 0 if r(i) = "4" .shapes("chart 3").chart.seriescollection(1).points(i + 1).format.line.weight = 1 next end end sub
this half answer since provides explanation on why code not plot individual line markers, fails give right way it. if want me take closer @ problem must provide worksheet can evaluate details of chart.
on other hand, code quite repetitive , therefore inefficient.
i discuss 2 issues.
pointobject not havebordermethod. reason why code not working. means cannot write code things like:.shapes("chart 3").chart.seriescollection(1).points(i + 1).border
if want know methodspointobject has, press f2 while on vbe. if want know more objects , methods can try analysistabs.com or dummies.com , excel-spreadsheet.com. again, able assist if share worksheet.you repeat 32 times code
.shapes("chart3").chart.seriescollection(1).points(i + 1)
can avoid declaring variablepof typepoint, assign value right after for-loop. give ideia how can achieve this, work properly, should share worksheet.sub tropical_cyclone_track_format() dim r range set r = activesheet.range("e24:e31") dim integer = 1 activesheet.shapes("chart 3").chart.seriescollection(1).points.count - 1 dim p point set p = activesheet.shapes("chart 3").chart.seriescollection(1).points(i + 1) if r(i) = "1" p p.markersize = xlmarkerstylecircle p.markerstyle = 2 p.markerbackgroundcolor = rgb(0, 0, 0) p.markerforegroundcolor = rgb(0, 0, 0) p.format.line.transparency = 0 p.format.line.weight = 1 end end if if r(i) = "2" p p.markersize = xlmarkerstylecircle p.markerstyle = 2 p.markerbackgroundcolor = rgb(0, 0, 0) p.markerforegroundcolor = rgb(0, 0, 0) p.format.line.transparency = 0 p.format.line.weight = 1 end end if if r(i) = "3" p p.markersize = xlmarkerstylecircle p.markerstyle = 2 p.markerbackgroundcolor = rgb(0, 0, 0) p.markerforegroundcolor = rgb(0, 0, 0) p.format.line.transparency = 0 p.format.line.weight = 1 end end if if r(i) = "4" p p.markersize = xlmarkerstylecircle p.markerstyle = 2 p.markerbackgroundcolor = rgb(0, 0, 0) p.markerforegroundcolor = rgb(0, 0, 0) p.format.line.transparency = 0 p.format.line.weight = 1 end end if next end sub
Comments
Post a Comment