Setting all the Chart Colours in VBA and also font sizes
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. |
Setting all the Chart Colours in VBA and also font sizes
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. |
Setting all the Chart Colours in VBA and also font sizes
Hi Michael,
I understand you are trying to fill the series bars on the chart, if so, please find the code snippet You need to update the RBG parameters are per your requirements. 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. |
Setting all the Chart Colours in VBA and also font sizes
Shiva,
I guess there was meant to be a End With statement in their, but also it just give me a Visual basic 400 error and no message. Michael "Shiva" wrote: Hi Michael, I understand you are trying to fill the series bars on the chart, if so, please find the code snippet You need to update the RBG parameters are per your requirements. 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. |
All times are GMT +1. The time now is 08:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com