locked
Chart default colors aren't available to VBA process RRS feed

  • Question

  • I've written a program that uses a UserForm to modify a chart in Excel.  The colors assigned to the chart by default are not available to the VBA process.  I've tried using the RGB function and the color returned is (255,255,255).  When I try to programmatically change the color, it doesn't work.

    If I manually change the color to one of the standard colors, the program works fine.

    Anyone seen anything like this?

    Thanks,

    Peter

    Wednesday, August 31, 2011 10:57 PM

Answers

  • Hi Peter,

    You can change the color of a serie by using the .Interior.Color property from the SeriesCollection.

    For example:

       ActiveSheet.ChartObjects("Chart 2").Activate
       ' change the series 2 into color Red
       ActiveChart.SeriesCollection(2).Interior.color = vbRed
    
    
     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    • Proposed as answer by danishani Sunday, March 11, 2012 1:02 AM
    • Marked as answer by danishani Tuesday, March 20, 2012 7:01 PM
    Saturday, March 3, 2012 6:00 PM

All replies

  • Try recording a macro of you manually changing the color. That will give you the code.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Thursday, September 1, 2011 7:42 AM
  • I've already written all the code, which works fine.  The issue is with the default colors Excel is assigning to the chart.

    One hint I've seen that the default colors aren't available to the VBA process is that once the chart is created, if you right-click the line and attempt to change the color, the color that is initially selected in the dropdown isn't the color on the chart.  With the standard chart colors if you try to change the color, the current color is the initial one in the dropdown.

    Rod: Recording a macro while changing the color records nothing. 

    Sub Macro5()
    '
    ' Macro5 Macro
    '
        ActiveSheet.ChartObjects("Chart 2").Activate
        ActiveChart.SeriesCollection(2).Select
    End Sub

    I'm using Excel 2007 SP1.  Is it supposed to record something useful?


    Peter Ekstrom
    Thursday, September 1, 2011 3:33 PM
  • Hi Peter,

    You can change the color of a serie by using the .Interior.Color property from the SeriesCollection.

    For example:

       ActiveSheet.ChartObjects("Chart 2").Activate
       ' change the series 2 into color Red
       ActiveChart.SeriesCollection(2).Interior.color = vbRed
    
    
     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    • Proposed as answer by danishani Sunday, March 11, 2012 1:02 AM
    • Marked as answer by danishani Tuesday, March 20, 2012 7:01 PM
    Saturday, March 3, 2012 6:00 PM