Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Setting the Colour of Individual Items within a Legend
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default Setting the Colour of Individual Items within a Legend
I am using the following Code to plot the chart, but Im not sure how to getit
to let me decide the colours of the Columns it displays

Sub Chrt_Incident_Age_Click()

Dim chtChart As Chart

' Remove Existing Chart

ActiveSheet.ChartObjects.Delete

' Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHARTS")

With chtChart

.ChartType = xlCylinderCol

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART DATA").Range("L11:X14"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Current Status"
.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

'.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

' The Parent property is used to set properties of the Chart.

With .Parent
.Top = Range("G3").Top
.Left = Range("G3").Left
.Width = Range("G3:R30").Width
.Height = Range("G3:R30").Height


End With

End With

ActiveChart.Legend.Select
Selection.Delete

End Sub

I tried the following code that was mentioned on here and it doesnot appear
to do anything. DOes anyone have any ideas?

For i = 1 To ActiveChart.Legend.LegendEntries.Count
ActiveChart.Legend.LegendEntries(i).LegendKey.Sele ct

With Selection.Interior
Select Case i
Case 1
.Color = RGB(0, 51, 153)
Case 2
.Color = RGB(64, 102, 178)
Case 3
.Color = RGB(128, 153, 204)
Case 4
.Color = RGB(102, 102, 255)
Case 5
.Color = RGB(140, 140, 255)
Case 6
.Color = RGB(178, 178, 255)
Case 7
end select
Next

I hope this will help you!!!

Best regards,
Shiva

"Michael Hudston" wrote:

As a reference this is the current VBA that I am using to display the chart.

Sub Cause_During_Corr_Acc_Click()

Dim chtChart As Chart

'Remove Existing Chart

ActiveSheet.ChartObjects.Delete

'Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject, Name:="CHART")

With chtChart

.ChartType = xlColumnClustered

'Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART DATA").Range("B17:C28"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Cause Defined During Corrective Action"
.Axes(xlCategory).Delete

'The Parent property is used to set properties of
'the Chart.

With .Parent
.Top = Range("D2").Top
.Left = Range("D2").Left
.Name = "chart_CDCA"
End With

End With

End Sub

"Michael Hudston" wrote:

I am creating all my charts in VBA, so they appear at a touch of a button. I
am looking to set the following properties, and cant seem to find them in the
help.

The Colours of the Columns in my Graph, so particular colums are always RED,
YELLOW, GREEN, etc.

The font size of the Chart Title and Legend.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default Setting the Colour of Individual Items within a Legend
Better to format the series themselves, legend formats will reflect same.

Dim sr As Series
Dim arr
Dim i

arr = Array(RGB(0, 51, 153), RGB(64, 102, 178), _
RGB(128, 153, 204), RGB(102, 102, 255), _
RGB(140, 140, 255), RGB(178, 178, 255))

i = -1
For Each sr In chtChart .SeriesCollection
i = i + 1
If i <= UBound(arr) Then sr.Interior.Color = arr(i)
Next

Unless you are using XL2007, with a default palette your interior formats
will not update to your particular RGB's. Instead they will match to the
closest matching colours that exist in the palette. If you want all your
charts in the workbook to adopt your colour sheme suggest you customize the
palette. In particular the bottom two rows of the palette.

Regards,
Peter T



"Michael Hudston" wrote in
message ...
I am using the following Code to plot the chart, but Im not sure how to
getit
to let me decide the colours of the Columns it displays

Sub Chrt_Incident_Age_Click()

Dim chtChart As Chart

' Remove Existing Chart

ActiveSheet.ChartObjects.Delete

' Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHARTS")

With chtChart

.ChartType = xlCylinderCol

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART DATA").Range("L11:X14"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Current Status"
.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

'.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

' The Parent property is used to set properties of the Chart.

With .Parent
.Top = Range("G3").Top
.Left = Range("G3").Left
.Width = Range("G3:R30").Width
.Height = Range("G3:R30").Height


End With

End With

ActiveChart.Legend.Select
Selection.Delete

End Sub

I tried the following code that was mentioned on here and it doesnot
appear
to do anything. DOes anyone have any ideas?

For i = 1 To ActiveChart.Legend.LegendEntries.Count
ActiveChart.Legend.LegendEntries(i).LegendKey.Sele ct

With Selection.Interior
Select Case i
Case 1
.Color = RGB(0, 51, 153)
Case 2
.Color = RGB(64, 102, 178)
Case 3
.Color = RGB(128, 153, 204)
Case 4
.Color = RGB(102, 102, 255)
Case 5
.Color = RGB(140, 140, 255)
Case 6
.Color = RGB(178, 178, 255)
Case 7
end select
Next

I hope this will help you!!!

Best regards,
Shiva

"Michael Hudston" wrote:

As a reference this is the current VBA that I am using to display the
chart.

Sub Cause_During_Corr_Acc_Click()

Dim chtChart As Chart

'Remove Existing Chart

ActiveSheet.ChartObjects.Delete

'Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHART")

With chtChart

.ChartType = xlColumnClustered

'Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART
DATA").Range("B17:C28"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Cause Defined During Corrective Action"
.Axes(xlCategory).Delete

'The Parent property is used to set properties of
'the Chart.

With .Parent
.Top = Range("D2").Top
.Left = Range("D2").Left
.Name = "chart_CDCA"
End With

End With

End Sub

"Michael Hudston" wrote:

I am creating all my charts in VBA, so they appear at a touch of a
button. I
am looking to set the following properties, and cant seem to find them
in the
help.

The Colours of the Columns in my Graph, so particular colums are always
RED,
YELLOW, GREEN, etc.

The font size of the Chart Title and Legend.




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default Setting the Colour of Individual Items within a Legend
Ok that is changin the colours, but they are all the same. One thing I have
identified is that they all appear to e points in the same series, which is
why they all have the same colour.


"Peter T" wrote:

Better to format the series themselves, legend formats will reflect same.

Dim sr As Series
Dim arr
Dim i

arr = Array(RGB(0, 51, 153), RGB(64, 102, 178), _
RGB(128, 153, 204), RGB(102, 102, 255), _
RGB(140, 140, 255), RGB(178, 178, 255))

i = -1
For Each sr In chtChart .SeriesCollection
i = i + 1
If i <= UBound(arr) Then sr.Interior.Color = arr(i)
Next

Unless you are using XL2007, with a default palette your interior formats
will not update to your particular RGB's. Instead they will match to the
closest matching colours that exist in the palette. If you want all your
charts in the workbook to adopt your colour sheme suggest you customize the
palette. In particular the bottom two rows of the palette.

Regards,
Peter T



"Michael Hudston" wrote in
message ...
I am using the following Code to plot the chart, but Im not sure how to
getit
to let me decide the colours of the Columns it displays

Sub Chrt_Incident_Age_Click()

Dim chtChart As Chart

' Remove Existing Chart

ActiveSheet.ChartObjects.Delete

' Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHARTS")

With chtChart

.ChartType = xlCylinderCol

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART DATA").Range("L11:X14"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Current Status"
.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

'.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

' The Parent property is used to set properties of the Chart.

With .Parent
.Top = Range("G3").Top
.Left = Range("G3").Left
.Width = Range("G3:R30").Width
.Height = Range("G3:R30").Height


End With

End With

ActiveChart.Legend.Select
Selection.Delete

End Sub

I tried the following code that was mentioned on here and it doesnot
appear
to do anything. DOes anyone have any ideas?

For i = 1 To ActiveChart.Legend.LegendEntries.Count
ActiveChart.Legend.LegendEntries(i).LegendKey.Sele ct

With Selection.Interior
Select Case i
Case 1
.Color = RGB(0, 51, 153)
Case 2
.Color = RGB(64, 102, 178)
Case 3
.Color = RGB(128, 153, 204)
Case 4
.Color = RGB(102, 102, 255)
Case 5
.Color = RGB(140, 140, 255)
Case 6
.Color = RGB(178, 178, 255)
Case 7
end select
Next

I hope this will help you!!!

Best regards,
Shiva

"Michael Hudston" wrote:

As a reference this is the current VBA that I am using to display the
chart.

Sub Cause_During_Corr_Acc_Click()

Dim chtChart As Chart

'Remove Existing Chart

ActiveSheet.ChartObjects.Delete

'Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHART")

With chtChart

.ChartType = xlColumnClustered

'Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART
DATA").Range("B17:C28"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Cause Defined During Corrective Action"
.Axes(xlCategory).Delete

'The Parent property is used to set properties of
'the Chart.

With .Parent
.Top = Range("D2").Top
.Left = Range("D2").Left
.Name = "chart_CDCA"
End With

End With

End Sub

"Michael Hudston" wrote:

I am creating all my charts in VBA, so they appear at a touch of a
button. I
am looking to set the following properties, and cant seem to find them
in the
help.

The Colours of the Columns in my Graph, so particular colums are always
RED,
YELLOW, GREEN, etc.

The font size of the Chart Title and Legend.





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default Setting the Colour of Individual Items within a Legend
I don't follow, what are "e points in the same series". Also I don't
understand the final conclusion at all.

Regards,
Peter T

"Michael Hudston" wrote in
message ...
Ok that is changin the colours, but they are all the same. One thing I
have
identified is that they all appear to e points in the same series, which
is
why they all have the same colour.


"Peter T" wrote:

Better to format the series themselves, legend formats will reflect same.

Dim sr As Series
Dim arr
Dim i

arr = Array(RGB(0, 51, 153), RGB(64, 102, 178), _
RGB(128, 153, 204), RGB(102, 102, 255), _
RGB(140, 140, 255), RGB(178, 178, 255))

i = -1
For Each sr In chtChart .SeriesCollection
i = i + 1
If i <= UBound(arr) Then sr.Interior.Color = arr(i)
Next

Unless you are using XL2007, with a default palette your interior formats
will not update to your particular RGB's. Instead they will match to the
closest matching colours that exist in the palette. If you want all your
charts in the workbook to adopt your colour sheme suggest you customize
the
palette. In particular the bottom two rows of the palette.

Regards,
Peter T



"Michael Hudston" wrote in
message ...
I am using the following Code to plot the chart, but Im not sure how to
getit
to let me decide the colours of the Columns it displays

Sub Chrt_Incident_Age_Click()

Dim chtChart As Chart

' Remove Existing Chart

ActiveSheet.ChartObjects.Delete

' Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHARTS")

With chtChart

.ChartType = xlCylinderCol

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART
DATA").Range("L11:X14"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Current Status"
.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

'.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

' The Parent property is used to set properties of the Chart.

With .Parent
.Top = Range("G3").Top
.Left = Range("G3").Left
.Width = Range("G3:R30").Width
.Height = Range("G3:R30").Height


End With

End With

ActiveChart.Legend.Select
Selection.Delete

End Sub

I tried the following code that was mentioned on here and it doesnot
appear
to do anything. DOes anyone have any ideas?

For i = 1 To ActiveChart.Legend.LegendEntries.Count
ActiveChart.Legend.LegendEntries(i).LegendKey.Sele ct

With Selection.Interior
Select Case i
Case 1
.Color = RGB(0, 51, 153)
Case 2
.Color = RGB(64, 102, 178)
Case 3
.Color = RGB(128, 153, 204)
Case 4
.Color = RGB(102, 102, 255)
Case 5
.Color = RGB(140, 140, 255)
Case 6
.Color = RGB(178, 178, 255)
Case 7
end select
Next

I hope this will help you!!!

Best regards,
Shiva

"Michael Hudston" wrote:

As a reference this is the current VBA that I am using to display the
chart.

Sub Cause_During_Corr_Acc_Click()

Dim chtChart As Chart

'Remove Existing Chart

ActiveSheet.ChartObjects.Delete

'Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHART")

With chtChart

.ChartType = xlColumnClustered

'Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART
DATA").Range("B17:C28"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Cause Defined During Corrective Action"
.Axes(xlCategory).Delete

'The Parent property is used to set properties of
'the Chart.

With .Parent
.Top = Range("D2").Top
.Left = Range("D2").Left
.Name = "chart_CDCA"
End With

End With

End Sub

"Michael Hudston" wrote:

I am creating all my charts in VBA, so they appear at a touch of a
button. I
am looking to set the following properties, and cant seem to find
them
in the
help.

The Colours of the Columns in my Graph, so particular colums are
always
RED,
YELLOW, GREEN, etc.

The font size of the Chart Title and Legend.







Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default Setting the Colour of Individual Items within a Legend
Peter,

Sorry, was a typo on my part, and also I think I have confused the issue.

Tha data is in a single series, and they are points in the series. I am
trying to set the colour of individual points, which each represent single
columns in the chart.

I could use,

ActiveChart.SeriesCollection(1).Points(x).Interior .Color = RGB(red, green,
blue) where x is the point umber (From 1 to 6 in this case).





"Peter T" wrote:

I don't follow, what are "e points in the same series". Also I don't
understand the final conclusion at all.

Regards,
Peter T

"Michael Hudston" wrote in
message ...
Ok that is changin the colours, but they are all the same. One thing I
have
identified is that they all appear to e points in the same series, which
is
why they all have the same colour.


"Peter T" wrote:

Better to format the series themselves, legend formats will reflect same.

Dim sr As Series
Dim arr
Dim i

arr = Array(RGB(0, 51, 153), RGB(64, 102, 178), _
RGB(128, 153, 204), RGB(102, 102, 255), _
RGB(140, 140, 255), RGB(178, 178, 255))

i = -1
For Each sr In chtChart .SeriesCollection
i = i + 1
If i <= UBound(arr) Then sr.Interior.Color = arr(i)
Next

Unless you are using XL2007, with a default palette your interior formats
will not update to your particular RGB's. Instead they will match to the
closest matching colours that exist in the palette. If you want all your
charts in the workbook to adopt your colour sheme suggest you customize
the
palette. In particular the bottom two rows of the palette.

Regards,
Peter T



"Michael Hudston" wrote in
message ...
I am using the following Code to plot the chart, but Im not sure how to
getit
to let me decide the colours of the Columns it displays

Sub Chrt_Incident_Age_Click()

Dim chtChart As Chart

' Remove Existing Chart

ActiveSheet.ChartObjects.Delete

' Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHARTS")

With chtChart

.ChartType = xlCylinderCol

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART
DATA").Range("L11:X14"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Current Status"
.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

'.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

' The Parent property is used to set properties of the Chart.

With .Parent
.Top = Range("G3").Top
.Left = Range("G3").Left
.Width = Range("G3:R30").Width
.Height = Range("G3:R30").Height


End With

End With

ActiveChart.Legend.Select
Selection.Delete

End Sub

I tried the following code that was mentioned on here and it doesnot
appear
to do anything. DOes anyone have any ideas?

For i = 1 To ActiveChart.Legend.LegendEntries.Count
ActiveChart.Legend.LegendEntries(i).LegendKey.Sele ct

With Selection.Interior
Select Case i
Case 1
.Color = RGB(0, 51, 153)
Case 2
.Color = RGB(64, 102, 178)
Case 3
.Color = RGB(128, 153, 204)
Case 4
.Color = RGB(102, 102, 255)
Case 5
.Color = RGB(140, 140, 255)
Case 6
.Color = RGB(178, 178, 255)
Case 7
end select
Next

I hope this will help you!!!

Best regards,
Shiva

"Michael Hudston" wrote:

As a reference this is the current VBA that I am using to display the
chart.

Sub Cause_During_Corr_Acc_Click()

Dim chtChart As Chart

'Remove Existing Chart

ActiveSheet.ChartObjects.Delete

'Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHART")

With chtChart

.ChartType = xlColumnClustered

'Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART
DATA").Range("B17:C28"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Cause Defined During Corrective Action"
.Axes(xlCategory).Delete

'The Parent property is used to set properties of
'the Chart.

With .Parent
.Top = Range("D2").Top
.Left = Range("D2").Left
.Name = "chart_CDCA"
End With

End With

End Sub

"Michael Hudston" wrote:

I am creating all my charts in VBA, so they appear at a touch of a
button. I
am looking to set the following properties, and cant seem to find
them
in the
help.

The Colours of the Columns in my Graph, so particular colums are
always
RED,
YELLOW, GREEN, etc.

The font size of the Chart Title and Legend.








Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default Setting the Colour of Individual Items within a Legend
OK understood. Looks like you know what to do but could change the example I
gave

Dim sr As Series
For Each sr In chtChart .SeriesCollection

Dim pt as point
'code
For Each pt In chtChart.SeriesCollection(1).Points
' etc

Regards,
Peter T



"Michael Hudston" wrote in
message ...
Peter,

Sorry, was a typo on my part, and also I think I have confused the issue.

Tha data is in a single series, and they are points in the series. I am
trying to set the colour of individual points, which each represent single
columns in the chart.

I could use,

ActiveChart.SeriesCollection(1).Points(x).Interior .Color = RGB(red, green,
blue) where x is the point umber (From 1 to 6 in this case).





"Peter T" wrote:

I don't follow, what are "e points in the same series". Also I don't
understand the final conclusion at all.

Regards,
Peter T

"Michael Hudston" wrote in
message ...
Ok that is changin the colours, but they are all the same. One thing I
have
identified is that they all appear to e points in the same series,
which
is
why they all have the same colour.


"Peter T" wrote:

Better to format the series themselves, legend formats will reflect
same.

Dim sr As Series
Dim arr
Dim i

arr = Array(RGB(0, 51, 153), RGB(64, 102, 178), _
RGB(128, 153, 204), RGB(102, 102, 255), _
RGB(140, 140, 255), RGB(178, 178, 255))

i = -1
For Each sr In chtChart .SeriesCollection
i = i + 1
If i <= UBound(arr) Then sr.Interior.Color = arr(i)
Next

Unless you are using XL2007, with a default palette your interior
formats
will not update to your particular RGB's. Instead they will match to
the
closest matching colours that exist in the palette. If you want all
your
charts in the workbook to adopt your colour sheme suggest you
customize
the
palette. In particular the bottom two rows of the palette.

Regards,
Peter T



"Michael Hudston" wrote in
message ...
I am using the following Code to plot the chart, but Im not sure how
to
getit
to let me decide the colours of the Columns it displays

Sub Chrt_Incident_Age_Click()

Dim chtChart As Chart

' Remove Existing Chart

ActiveSheet.ChartObjects.Delete

' Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHARTS")

With chtChart

.ChartType = xlCylinderCol

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART
DATA").Range("L11:X14"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Current Status"
.SeriesCollection(1).XValues = "='BASIC CHART
DATA'!$M$4:$X$10"

'.SeriesCollection(1).XValues = "='BASIC CHART
DATA'!$M$4:$X$10"

' The Parent property is used to set properties of the Chart.

With .Parent
.Top = Range("G3").Top
.Left = Range("G3").Left
.Width = Range("G3:R30").Width
.Height = Range("G3:R30").Height


End With

End With

ActiveChart.Legend.Select
Selection.Delete

End Sub

I tried the following code that was mentioned on here and it doesnot
appear
to do anything. DOes anyone have any ideas?

For i = 1 To ActiveChart.Legend.LegendEntries.Count
ActiveChart.Legend.LegendEntries(i).LegendKey.Sele ct

With Selection.Interior
Select Case i
Case 1
.Color = RGB(0, 51, 153)
Case 2
.Color = RGB(64, 102, 178)
Case 3
.Color = RGB(128, 153, 204)
Case 4
.Color = RGB(102, 102, 255)
Case 5
.Color = RGB(140, 140, 255)
Case 6
.Color = RGB(178, 178, 255)
Case 7
end select
Next

I hope this will help you!!!

Best regards,
Shiva

"Michael Hudston" wrote:

As a reference this is the current VBA that I am using to display
the
chart.

Sub Cause_During_Corr_Acc_Click()

Dim chtChart As Chart

'Remove Existing Chart

ActiveSheet.ChartObjects.Delete

'Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHART")

With chtChart

.ChartType = xlColumnClustered

'Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART
DATA").Range("B17:C28"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Cause Defined During Corrective Action"
.Axes(xlCategory).Delete

'The Parent property is used to set properties of
'the Chart.

With .Parent
.Top = Range("D2").Top
.Left = Range("D2").Left
.Name = "chart_CDCA"
End With

End With

End Sub

"Michael Hudston" wrote:

I am creating all my charts in VBA, so they appear at a touch of
a
button. I
am looking to set the following properties, and cant seem to find
them
in the
help.

The Colours of the Columns in my Graph, so particular colums are
always
RED,
YELLOW, GREEN, etc.

The font size of the Chart Title and Legend.










Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default Setting the Colour of Individual Items within a Legend
THanks Peter, Ill give that a go, and sorry for the confusion.

"Peter T" wrote:

OK understood. Looks like you know what to do but could change the example I
gave

Dim sr As Series
For Each sr In chtChart .SeriesCollection

Dim pt as point
'code
For Each pt In chtChart.SeriesCollection(1).Points
' etc

Regards,
Peter T



"Michael Hudston" wrote in
message ...
Peter,

Sorry, was a typo on my part, and also I think I have confused the issue.

Tha data is in a single series, and they are points in the series. I am
trying to set the colour of individual points, which each represent single
columns in the chart.

I could use,

ActiveChart.SeriesCollection(1).Points(x).Interior .Color = RGB(red, green,
blue) where x is the point umber (From 1 to 6 in this case).





"Peter T" wrote:

I don't follow, what are "e points in the same series". Also I don't
understand the final conclusion at all.

Regards,
Peter T

"Michael Hudston" wrote in
message ...
Ok that is changin the colours, but they are all the same. One thing I
have
identified is that they all appear to e points in the same series,
which
is
why they all have the same colour.


"Peter T" wrote:

Better to format the series themselves, legend formats will reflect
same.

Dim sr As Series
Dim arr
Dim i

arr = Array(RGB(0, 51, 153), RGB(64, 102, 178), _
RGB(128, 153, 204), RGB(102, 102, 255), _
RGB(140, 140, 255), RGB(178, 178, 255))

i = -1
For Each sr In chtChart .SeriesCollection
i = i + 1
If i <= UBound(arr) Then sr.Interior.Color = arr(i)
Next

Unless you are using XL2007, with a default palette your interior
formats
will not update to your particular RGB's. Instead they will match to
the
closest matching colours that exist in the palette. If you want all
your
charts in the workbook to adopt your colour sheme suggest you
customize
the
palette. In particular the bottom two rows of the palette.

Regards,
Peter T



"Michael Hudston" wrote in
message ...
I am using the following Code to plot the chart, but Im not sure how
to
getit
to let me decide the colours of the Columns it displays

Sub Chrt_Incident_Age_Click()

Dim chtChart As Chart

' Remove Existing Chart

ActiveSheet.ChartObjects.Delete

' Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHARTS")

With chtChart

.ChartType = xlCylinderCol

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART
DATA").Range("L11:X14"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Current Status"
.SeriesCollection(1).XValues = "='BASIC CHART
DATA'!$M$4:$X$10"

'.SeriesCollection(1).XValues = "='BASIC CHART
DATA'!$M$4:$X$10"

' The Parent property is used to set properties of the Chart.

With .Parent
.Top = Range("G3").Top
.Left = Range("G3").Left
.Width = Range("G3:R30").Width
.Height = Range("G3:R30").Height


End With

End With

ActiveChart.Legend.Select
Selection.Delete

End Sub

I tried the following code that was mentioned on here and it doesnot
appear
to do anything. DOes anyone have any ideas?

For i = 1 To ActiveChart.Legend.LegendEntries.Count
ActiveChart.Legend.LegendEntries(i).LegendKey.Sele ct

With Selection.Interior
Select Case i
Case 1
.Color = RGB(0, 51, 153)
Case 2
.Color = RGB(64, 102, 178)
Case 3
.Color = RGB(128, 153, 204)
Case 4
.Color = RGB(102, 102, 255)
Case 5
.Color = RGB(140, 140, 255)
Case 6
.Color = RGB(178, 178, 255)
Case 7
end select
Next

I hope this will help you!!!

Best regards,
Shiva

"Michael Hudston" wrote:

As a reference this is the current VBA that I am using to display
the
chart.

Sub Cause_During_Corr_Acc_Click()

Dim chtChart As Chart

'Remove Existing Chart

ActiveSheet.ChartObjects.Delete

'Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Whe=xlLocationAsObject,
Name:="CHART")

With chtChart

.ChartType = xlColumnClustered

'Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART
DATA").Range("B17:C28"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Cause Defined During Corrective Action"
.Axes(xlCategory).Delete

'The Parent property is used to set properties of
'the Chart.

With .Parent
.Top = Range("D2").Top
.Left = Range("D2").Left
.Name = "chart_CDCA"
End With

End With

End Sub

"Michael Hudston" wrote:

I am creating all my charts in VBA, so they appear at a touch of
a
button. I
am looking to set the following properties, and cant seem to find
them
in the
help.

The Colours of the Columns in my Graph, so particular colums are
always
RED,
YELLOW, GREEN, etc.

The font size of the Chart Title and Legend.











Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Undelete Legend Items Sloth Charts and Charting in Excel 3 April 3rd 23 10:54 AM
Change order of items in a legend bmac Charts and Charting in Excel 1 March 11th 10 04:43 PM
Chart Legend Items: hide/show Richard Ahlvin Charts and Charting in Excel 5 September 5th 05 03:04 PM
Order of items in legend Darryl Charts and Charting in Excel 3 July 20th 05 03:40 PM
XY Scatter Legend when Edit Individual Markers John Charts and Charting in Excel 6 June 13th 05 03:06 PM


All times are GMT +1. The time now is 04:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement