none
Excel VB Script Won't Change Line Color in Scatter Chart

    Question

  • I have an Excel 2007 workbook with a VB macro that creates series' in scatter charts, then tries to format the line color, weight and type based on the name of each series.  The weight and dash type formatting work fine, but the line color won't change.  The code is inserted here.

    For Each ChObj In ActiveSheet.ChartObjects With ChObj.Chart For col = 2 To NumRanges + 1 ' add series's to the chart .SeriesCollection.Add Source:=Range(Cells(StartRow, col), Cells(StartRow + 49, col)), Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=False, Replace:=False .SeriesCollection(col - 1).XValues = Range(Cells(StartRow, 1), Cells(StartRow + 49, 1)) .SeriesCollection(col - 1).Name = Range(Cells(1, col), Cells(1, col)) ' If limit ranges, set to thin dotted black If InStr(1, .SeriesCollection(col - 1).Name, "Limit", vbTextCompare) <> 0 Then ' set line to thin black dotted if it's a limit range

    .SeriesCollection(col - 1).Format.Line.DashStyle = msoLineDash .SeriesCollection(col - 1).Format.Line.Weight = 1.5 .SeriesCollection(col - 1).Format.Line.ForeColor.RGB = vbBlack ' this line does not change the color!

    Next col End With Next ChObj


    Everything works fine except the line that tries to modify the line color.  It does not throw any errors, just has no effect, i.e. the color does not change.  I have confirmed that I can read the color value using the same construct (Color = .SeriesCollection(col - 1).Format.Line.ForeColor.RGB) and it returns the correct existing color value, then I try to change the color value, immediately read it again and it returns the same value as before, confirming what I already knew, that the color didn't change!  I have also tried ...RGB = 0, ...RGB = RGB(0, 0, 0), etc, etc, with no luck.

    Any ideas?

    • Moved by Bill_Stewart Friday, March 16, 2012 2:21 PM Moving to more appropriate forum (From:The Official Scripting Guys Forum!)
    Friday, March 16, 2012 2:13 PM

Answers

  • Try this (Doing this from Memory hence it is UNTESTED)

    '~~> Don't remember the RGB for Black. Replace it appropriately
    .SeriesCollection(col - 1).Border.Color = RGB(255, 127, 0)

    Or try this

    .SeriesCollection(col-1).Interior.Color = vbBlack


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    • Marked as answer by TBCh Saturday, March 17, 2012 3:33 PM
    Saturday, March 17, 2012 2:02 PM

All replies

  • You've hit the Scripting Guy's forum.  You want the VBA forum. 

    Moderators, please move.


    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Friday, March 16, 2012 2:17 PM
  • Sorry.  I don't do this very often so I'm not familiar with the most appropriate forum.
    Friday, March 16, 2012 2:48 PM
  • There are so many forums, it can be confusing.  But your post is in the right place now, and you should get an answer soon.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Friday, March 16, 2012 2:51 PM
  • Try:

    .SeriesCollection(col-1).Format.Line.ForeColor.RGB=RGB(0,0,vbBlack)

    You could also use just =RGB(0, 0, 0)


    HTH,

    Eric

    Tu ne cede malis sed contra audentior ito

    Friday, March 16, 2012 6:02 PM
  • Didn't work.  I don't think there is any difference between what you suggest and what I am currently doing.  vbBlack, RGB(0, 0, vbBlack), RGB(0, 0, 0), 0, etc. are all just different ways of saying "zero".  The problem is that the color value is not changing.  I tried other color values besides zero (thinking maybe zero was a "special" value) and it still does not work.  If I execute this snippet in the debugger:

                            TTT = .SeriesCollection(col - 1).Format.Line.ForeColor.RGB
                            .SeriesCollection(col - 1).Format.Line.ForeColor.RGB = 0  ' black
                            TTT = .SeriesCollection(col - 1).Format.Line.ForeColor.RGB
    TTT contains the correct color value after line 1, and it still contains the same value after line 3.  So I can read the color value, but I can't overwrite it.  Is there some kind of "protection" or flag that needs to be toggled to allow setting color values?
    Friday, March 16, 2012 8:05 PM
  • Try this (Doing this from Memory hence it is UNTESTED)

    '~~> Don't remember the RGB for Black. Replace it appropriately
    .SeriesCollection(col - 1).Border.Color = RGB(255, 127, 0)

    Or try this

    .SeriesCollection(col-1).Interior.Color = vbBlack


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    • Marked as answer by TBCh Saturday, March 17, 2012 3:33 PM
    Saturday, March 17, 2012 2:02 PM
  • Thank you so much!  Your first suggestion did the trick (.SeriesCollection(col - 1).Border.Color = vbBlack).  The weird thing is that it also resets the dash style back to a solid line so I needed to do the color change first, then the line style changes.  I'm still left wondering why the Excel object model help documentation led me down the wrong path.  Thanks everyone for all your help.

    - Tom

    Saturday, March 17, 2012 3:37 PM