none
Copy format of SINGLE point in a chart (VBA, in Excel 2007/2010)

    Question

  • Hi everybody,

     I am trying to copy the format (fill style) from a single point of a chart to an other single point (or series) of other charts.

    For example: I have a pie and then a stacked bars chart; the pie has 6 values; the stacked bar chart has 6 series (same number).
    I want the fill style of each series of the stacked bars chart to be the same of the fill style of the corresponding point in the pie.

    I am afraid the only way is to write a long "select ... case..." to get what kind of fill style is used in the point of the pie and then apply it in VBA with a property-by-property approach.
    Example (simplified):
    CASE: POINT is with SOLID FILL ==> copy the single color
    CASE: POINT is with Gradient ==> copy all colors and their "position"
    CASE: POINT is with Image ==> copy image path & style (single image, stacked...)

    Is this right? is this long-to-write solution the only solution I can find?

    thanks

    Cosimo - Milan, Italy 

    Saturday, August 09, 2014 11:04 AM

Answers

  • Hello Cosimo,

    The code below is very simplified charts but hopefully will point you in the right direction. If you need more help then need to see an example workbook with both data and the charts so please post on OneDrive.

    Guidelines to post a copy on OneDrive

    1. Zip your workbooks. I prefer that you do not just save to OneDrive. (To Zip a file, in Windows Explorer  Right click on the selected file and select Send to -> Compressed (zipped) folder.)
    2. Go to this link.  https://onedrive.live.com
    3. Use the same login Id and Password that you use for this forum.
    4. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded and select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    5. Right click the file on OneDrive and select Share.
    6. Do NOT fill in the form; "Select Get a Link" on the left side.
    7. Click the button "Create a Link"
    8. Click in the box where the link is created and it will highlight.
    9. Copy the link and paste into your reply on this forum.

    Example code. Note that the space and underscore at the end of a line is a line break in an otherwise single line of code.

    Sub ReColorCharts()

        Dim chtOne As Object
        Dim chtTwo As Object
        Dim i As Long
        Dim lngIndex As Long
       
        Set chtOne = ActiveSheet.ChartObjects("Chart 1")    'Pie chart
        Set chtTwo = ActiveSheet.ChartObjects("Chart 2")    'Stacked bar chart
       
        With chtOne.Chart
            For i = 1 To .SeriesCollection(1).Points.Count
                chtTwo.Chart.SeriesCollection(1).Points(i).Interior.Color = _
                    .SeriesCollection(1).Points(i).Interior.Color
            Next i
        End With

    End Sub


    Regards, OssieMac

    Sunday, August 10, 2014 11:18 AM
  • Hi OssieMac,

     in fact your solution works for solid fill only but I thank you for your effort.
    I found a partial solution here: http://chat.stackoverflow.com/rooms/17116/justinjdavies
    and I am going to impreve that.

    Thanks

    Cosimo

    Monday, August 11, 2014 9:36 PM

All replies

  • Hello Cosimo,

    The code below is very simplified charts but hopefully will point you in the right direction. If you need more help then need to see an example workbook with both data and the charts so please post on OneDrive.

    Guidelines to post a copy on OneDrive

    1. Zip your workbooks. I prefer that you do not just save to OneDrive. (To Zip a file, in Windows Explorer  Right click on the selected file and select Send to -> Compressed (zipped) folder.)
    2. Go to this link.  https://onedrive.live.com
    3. Use the same login Id and Password that you use for this forum.
    4. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded and select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    5. Right click the file on OneDrive and select Share.
    6. Do NOT fill in the form; "Select Get a Link" on the left side.
    7. Click the button "Create a Link"
    8. Click in the box where the link is created and it will highlight.
    9. Copy the link and paste into your reply on this forum.

    Example code. Note that the space and underscore at the end of a line is a line break in an otherwise single line of code.

    Sub ReColorCharts()

        Dim chtOne As Object
        Dim chtTwo As Object
        Dim i As Long
        Dim lngIndex As Long
       
        Set chtOne = ActiveSheet.ChartObjects("Chart 1")    'Pie chart
        Set chtTwo = ActiveSheet.ChartObjects("Chart 2")    'Stacked bar chart
       
        With chtOne.Chart
            For i = 1 To .SeriesCollection(1).Points.Count
                chtTwo.Chart.SeriesCollection(1).Points(i).Interior.Color = _
                    .SeriesCollection(1).Points(i).Interior.Color
            Next i
        End With

    End Sub


    Regards, OssieMac

    Sunday, August 10, 2014 11:18 AM
  • Hi OssieMac,

     in fact your solution works for solid fill only but I thank you for your effort.
    I found a partial solution here: http://chat.stackoverflow.com/rooms/17116/justinjdavies
    and I am going to impreve that.

    Thanks

    Cosimo

    Monday, August 11, 2014 9:36 PM