N
Naum
I am building a workbook with a signle datasheet (Summary_Worksheet) and many
chart sheets. Only a 15 columns by 32 rows area has data for charts.
One chart is a filled radar type chart, rest are simple bar charts.
Series for radar chart are based on columns from the datasheet. Each line
(colored distinctivly) represent a column, each spike represent a row.
SourceData Series Values for radars are, for example:
=Summary_Worksheet!$K$1068:$K$1099
Series for bar charts are based on rows:
=Summary_Worksheet!$I$1102:$W$1102
Series for bar charts are built on rows with 15 bars on each, representing
15 values in the same columns, used in radar charts.
I would like to control the color of chart items, listed below by colors of
specific cells in the same column where data for a particular series are
located. I have a finite number of series (15). Looks like I need code
something like that, probably in ThisWorkbook:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Type = RadarChart
For i=1,15
' How do I name series?
' let's say I put colors in row 1067, columns I-W (column numbers 9-23)
' line color on the radar chart
' for i=1 Range will be ($9$1067) which is first colored cell...
' not clear how to tie Series(i) to a series based on a specific column
Series(i).Line.Color = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color
'each series has a checkbox which allows to hide/unhide a column (we hide
when no data).
'each checkbox has its own name - in my case by a column it represents:
cbI40, cbK40, cbL40 etc
' row 40 is used because it contains names of series, which are used in
legend and labels:
' =Summary_Worksheet!$J$40 for example
' perhaps that is how I can name my series for radar?...
cbCheck_Box_Name.Background = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color
'
Series(i).LegendKey.Color = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color
End For
End If
If Sh.Type = BarChart
For i=1,15
DataPoint.Color for Series(i) = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color
End For
End If
Thank you!
chart sheets. Only a 15 columns by 32 rows area has data for charts.
One chart is a filled radar type chart, rest are simple bar charts.
Series for radar chart are based on columns from the datasheet. Each line
(colored distinctivly) represent a column, each spike represent a row.
SourceData Series Values for radars are, for example:
=Summary_Worksheet!$K$1068:$K$1099
Series for bar charts are based on rows:
=Summary_Worksheet!$I$1102:$W$1102
Series for bar charts are built on rows with 15 bars on each, representing
15 values in the same columns, used in radar charts.
I would like to control the color of chart items, listed below by colors of
specific cells in the same column where data for a particular series are
located. I have a finite number of series (15). Looks like I need code
something like that, probably in ThisWorkbook:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Type = RadarChart
For i=1,15
' How do I name series?
' let's say I put colors in row 1067, columns I-W (column numbers 9-23)
' line color on the radar chart
' for i=1 Range will be ($9$1067) which is first colored cell...
' not clear how to tie Series(i) to a series based on a specific column
Series(i).Line.Color = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color
'each series has a checkbox which allows to hide/unhide a column (we hide
when no data).
'each checkbox has its own name - in my case by a column it represents:
cbI40, cbK40, cbL40 etc
' row 40 is used because it contains names of series, which are used in
legend and labels:
' =Summary_Worksheet!$J$40 for example
' perhaps that is how I can name my series for radar?...
cbCheck_Box_Name.Background = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color
'
Series(i).LegendKey.Color = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color
End For
End If
If Sh.Type = BarChart
For i=1,15
DataPoint.Color for Series(i) = Worksheets("Summary_Worksheet").Range($(8 +
i)$1067)).Color
End For
End If
Thank you!