Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Giant font sizes in chart text boxes | Charts and Charting in Excel | |||
Change all text one font size up with various font sizes used. | New Users to Excel | |||
Excel 2003 font colours and cell colours | Excel Discussion (Misc queries) | |||
Font Sizes | Excel Programming | |||
Setting row & column sizes | Excel Programming |