Copy and paste styles of diagram

[vangog]

Hello,

I have many diagrams with about 15 data sets and I need to copy the styles of lines from one diagram, and copy the styles to the rest of the diagrams (after been selected). This is to do on very old Excel. I have 30 sheets - every contains 3 diagrams like that. So too hard to do it manially. I need to set the color of the like, color of the mark and the style of the mark (disabled/enabled). Can you please help with some VBA script to do?

[Aussiebear]

Once again.... attach a workbook with sample diagrams. Attachments (images) are nearly worthless to us.

[vangog]

I added data to separate file, 3 lists of data and one list of diagrams test.xls (diagram #1 source link corrected 9:16 AM)

[snb]

But you didn't indicate what should be changed into what.

[vangog]

There is problem that every column uses different color for curves. I need to copy the first diagram styles and paste them onto the selected diagrams. Possibly using VBA script. I would do that with the rest of the diagrams. I want to have all lines in same colors, same settings as in the first diagram in 'G30'. There is a lot of columns in that tables and setting every diagram colors would be very very hard.

[snb]

Fisrt you should remove dataseries 'days' form the first diagram.

Then run:

Sub M_snb()
   For Each it In Sheet4.ChartObjects
     For j = 1 To it.Chart.SeriesCollection.Count
        it.Chart.SeriesCollection(j).Format.Line.ForeColor.ObjectThemeColor = j
        it.Chart.SeriesCollection(j).MarkerStyle = j \ 5
        it.Chart.SeriesCollection(j).MarkerSize = 3
        it.Chart.SeriesCollection(j).MarkerForegroundColor = j \ 5
     Next
   Next
End Sub

[vangog]

Fisrt you should remove dataseries 'days' form the first diagram.

Then run:

Thank you.

[vangog]

How to modify the code to print the values from these properties to some sheet? I would write a macro but I need to get the values from the properties first. I mean something like:

For Each it In Worksheets("G7").ChartObjects
     For j = 1 To it.Chart.SeriesCollection.Count
        Worksheets("t").Cells(i, j) = it.Chart.SeriesCollection(j).Format.Line.ForeColor.ObjectThemeColor
        Worksheets("t").Cells(i, j + 1) = it.Chart.SeriesCollection(j).MarkerStyle
        Worksheets("t").Cells(i, j + 2) = it.Chart.SeriesCollection(j).MarkerSize
        Worksheets("t").Cells(i, j + 3) = it.Chart.SeriesCollection(j).MarkerForegroundColor
     Next
   Next

[georgiboy]

Perhaps something along the lines of:

Sub test()    
    Dim it As ChartObject, j As Integer, i As Integer, r As Integer, wsT As Worksheet
    
    Set wsT = Sheets("t")
    
    For Each it In Worksheets("G7").ChartObjects
        For j = 1 To it.Chart.SeriesCollection.Count
            r = r + 1
            With it.Chart.SeriesCollection(j)
                wsT.Cells(r, 1) = .Format.Line.ForeColor
                wsT.Cells(r, 2) = .MarkerStyle
                wsT.Cells(r, 3) = .MarkerSize
                wsT.Cells(r, 4) = .MarkerForegroundColor
            End With
        Next
    Next
End Sub

[vangog]

Perhaps something ...

Thank you. I have modified your code to work on ActiveChart, but I got error on the line
wsT.Cells(r, 1) = .Format.Line.ForeColor:

Sub extract_chart_styles()
    Dim it As ChartObject, j As Integer, i As Integer, r As Integer, wsT As Worksheet
    
    Set wsT = Sheets("t")
    If TypeName(Selection) <> "ChartArea" Then
       MsgBox "Please select ChartArea, not" + TypeName(Selection)
    Exit Sub
    End If
    
    For j = 1 To ActiveChart.SeriesCollection.Count
        r = r + 1
        With ActiveChart.SeriesCollection(j)
            wsT.Cells(r, 1) = .Format.Line.ForeColor
            wsT.Cells(r, 2) = .MarkerStyle
            wsT.Cells(r, 3) = .MarkerSize
            wsT.Cells(r, 4) = .MarkerForegroundColor
        End With
    Next
End Sub

It looks like in old Excel the member Format does not exist. However there is a Fill member. So this works when I replace Format.Line to Fill.

extract_chart_styles()
    Dim it As ChartObject, j As Integer, i As Integer, r As Integer, wsT As Worksheet
    
    Set wsT = Sheets("t")
    If TypeName(Selection) <> "ChartArea" Then
       MsgBox "Please select ChartArea, not" + TypeName(Selection)
    Exit Sub
    End If
    
    For j = 1 To ActiveChart.SeriesCollection.Count
        r = r + 1
        With ActiveChart.SeriesCollection(j)
            wsT.Cells(r, 1) = .Fill.ForeColor
            wsT.Cells(r, 2) = .MarkerStyle
            wsT.Cells(r, 3) = .MarkerSize
            wsT.Cells(r, 4) = .MarkerForegroundColor
        End With
    Next
End Sub

[vangog]

So far I have two procedures. One to extract styles from selected Chart. And the next one to set the styles... That one takes the table in sheets("t") as source. But I have problem setting the styles with an error. Can you please help to finish this?

Sub extract_styles_of_specific_chart()
    Dim it As ChartObject, j As Integer, i As Integer, r As Integer, wsT As Worksheet
    
    Set wsT = Sheets("t")
    If TypeName(Selection) <> "ChartArea" Then
       MsgBox "Please select ChartArea, not" + TypeName(Selection)
    Exit Sub
    End If
    
    For j = 1 To ActiveChart.SeriesCollection.Count
        r = r + 1
        With ActiveChart.SeriesCollection(j)
            wsT.Cells(r, 1) = .Fill.ForeColor
            wsT.Cells(r, 2) = .MarkerStyle
            wsT.Cells(r, 3) = .MarkerSize
            wsT.Cells(r, 4) = .MarkerForegroundColor
        End With
    Next
End Sub

Try to set the styles on charts of some active Sheet of Charts:

Sub set_chart_styles_from_table()

    Dim wsS As Worksheet, it As ChartObject, table As Range, c As Integer, l As Integer, ChartSheetName As String
    ChartSheetName = ActiveSheet.Name
    Set wsS = Sheets("t") ' Here is source table with Chart styles values
    Sheets("t").Activate
    Set table = wsS.Range("A1", Range("D11"))
    Sheets(ChartSheetName).Activate
    For l = 1 To table.Rows.Count
       For Each it In ActiveSheet.ChartObjects
         it.Chart.SeriesCollection(l).Fill.ForeColor = table.Rows(l).Columns.Cells(1).Value
         it.Chart.SeriesCollection(l).MarkerStyle = table.Rows(l).Columns.Cells(2).Value
         it.Chart.SeriesCollection(l).MarkerSize = table.Rows(l).Columns.Cells(3).Value
         it.Chart.SeriesCollection(l).MarkerForegroundColor = table.Rows(l).Columns.Cells(4).Value
       Next it
    Next
End Sub

The error here:
error 450: Wrong number of arguments or invalid property assignment ...

The value of table.Rows(l).Columns.Cells(1).Value
checked in a watch and it is Correct
Example values from the first row:

10077403

-4142

5

58

[georgiboy]

See attached

[vangog]

Thank you but can you please save the file as .xls? Or just paste the VBA code. I cannot open your file.

Edit:
I managed to open it in LibreOffice. Now studing the code.

[vangog]

Currently I am having this problem:

it.Chart.SeriesCollection(l).Fill.ForeColor.RGB = RGB(R, G, B)

Generates error wrong number of arguments or invalid property assigment.

[vangog]

See attached

Is it possible that the fill property would be read only? For any reason? In the old excel there is no property format or line, so I wonder how to set color on Windows XP. Also I have noticed .MarkerForegroundColor is most times 58 : I copy the rows here:

58
58
58
58
58
57
10
58
58
58
58
57
57
46

And it means that it sets to black color. Which is pretty odd.