If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Format Color on a Pivot Chart
Hi Everyone.
Sorry if this is getting posted twice. I am trying to format colors of the area table. Below is the code for the entire setup and I am running multiple charts. The code below is trying to set collection using an Enum and it is failing. 'ActiveChart.SeriesCollection(1).Points(1).Select 'With Selection.Interior ' .ColorIndex.CustomerColors = PaleOrange 'End With Any help is appreciated Full code below Option Explicit Public i As Integer Public iCtr As Integer Public strQuery As String Public strQuery1 As String Public strQuery2 As String Public strBusType As String Public strBusType1 As String Public strBusType2 As String Public Enum CustomColors PaleOrange = 8183294 'RGB(254, 221, 124) PaleGreen = 8183511 'RGB(215, 222, 124) PaleBlue = 14009007 'RGB(175, 194, 213) PaleViolet = 11173238 'RGB(118, 125, 170) PalePurple = 7486603 'RGB(139, 60, 114) Blue1 = 8281923 'RGB(67, 95, 126) Blue2 = 12029799 'RGB(103, 143, 183) Blue3 = 15984349 'RGB(221, 230, 243) Gray1 = 13354187 'RGB(203, 196, 203) Gray2 = 13354699 'RGB(203, 198, 203) End Enum Public Sub Begin(StrWhatever As String) If StrWhatever = "All" Then iCtr = 2 strQuery1 = "qry_OPRiskBusiness" strQuery2 = "qry_OPRiskEventCategory" strBusType1 = "Business" strBusType2 = "EventCategory" ElseIf StrWhatever = "OP Risk Business" Then iCtr = 1 strQuery1 = "qry_OPRiskBusiness" strBusType1 = "Business" ElseIf StrWhatever = "OP Risk Event Category" Then iCtr = 1 strQuery2 = "qry_OPRiskEventCategory" strBusType2 = "EventCatagory" Else MsgBox "Something is not right", vbOKOnly Exit Sub End If End Sub Public Sub CreatePivotRange() ActiveWorkbook.Names.Add Name:="BusinessPivot", RefersToR1C1:= _ "=OFFSET(qry_OPRiskBusiness!R1C1,0,0,COUNTA(qry_OP RiskBusiness! C1),COUNTA(qry_OPRiskBusiness!R1))" ActiveWorkbook.Names.Add Name:="EventCategoryPivot", RefersToR1C1:= _ "=OFFSET(qry_OPRiskEventCategory!R1C1,0,0,COUN TA (qry_OPRiskEventCategory!C1),COUNTA(qry_OPRiskEven tCategory!R1))" End Sub Public Sub CreateCharts() i = 1 Do While i = iCtr If i = 1 Then strQuery = strQuery1 strBusType = strBusType1 Else strQuery = strQuery2 strBusType = strBusType2 End If If i = 1 Then ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "BusinessPivot").CreatePivotTable TableDestination:= _ "[TestExport_MK.xls]qry_OPRiskBusiness!R1C8", TableName:=strBusType, _ DefaultVersion:=xlPivotTableVersion10 Else ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "EventCategoryPivot").CreatePivotTable TableDestination:= _ "[TestExport_MK.xls]qry_OPRiskEventCategory!R1C8", TableName:=strBusType, _ DefaultVersion:=xlPivotTableVersion10 End If Sheets(strQuery).Select With ActiveSheet.PivotTables(strBusType) .ColumnGrand = False .EnableDrilldown = False .RowGrand = False .SaveData = False .NullString = "0" .RepeatItemsOnEachPrintedPage = False End With ActiveSheet.PivotTables(strBusType).AddFields _ RowFields:=Array("Year", "Quarter"), _ ColumnFields:=strBusType, PageFields:="Region" ActiveSheet.PivotTables(strBusType).PivotFields ("NetAmount_USD1").Orientation = xlDataField Range("H1").Select Charts.Add ActiveChart.Location xlLocationAsNewSheet, strBusType ActiveWorkbook.ShowPivotTableFieldList = False ActiveChart.ChartType = xlAreaStacked ActiveChart.Location Whe=xlLocationAsNewSheet With Selection.Border .Weight = xlHairline .LineStyle = xlAutomatic End With i = i + 1 Loop End Sub Public Sub ChartFormat(strRegion As String) i = 1 Do While i = iCtr If i = 1 Then strQuery = strQuery1 strBusType = strBusType1 Else strQuery = strQuery2 strBusType = strBusType2 End If 'Select the named chart Sheets(strBusType).Select 'Formats the chart 'Formats the charts borders ActiveChart.HasPivotFields = False ActiveChart.PlotArea.Select ActiveChart.Axes(xlValue).MajorGridlines.Select With Selection.Border .ColorIndex = 57 .Weight = xlHairline .LineStyle = xlDot End With 'Formats the Plot Area ActiveChart.PlotArea.Select With Selection.Border .ColorIndex = 16 .Weight = xlThin .LineStyle = xlContinuous End With With Selection.Interior .ColorIndex = 2 .PatternColorIndex = 1 .Pattern = xlSolid End With 'Formats the Legend ActiveChart.Legend.Select With Selection.Border .Weight = xlHairline .LineStyle = xlNone End With Selection.Shadow = False Selection.Interior.ColorIndex = xlAutomatic Selection.AutoScaleFont = True With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 9 End With Selection.Position = xlBottom 'Add the title / With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = strRegion & " Losses By " & strBusType & " (MM)" .HasLegend = True '.Legend.Select 'Selection.Position = xlBottom .Axes(xlValue).Select Selection.TickLabels.NumberFormat = "$#,##0.0" .Axes(xlCategory).Select End With With Selection .MajorTickMark = xlOutside .MinorTickMark = xlNone .TickLabelPosition = xlLow End With 'ActiveChart.SeriesCollection(1).Points(1).Select 'With Selection.Interior ' .ColorIndex.CustomerColors = PaleOrange 'End With i = i + 1 Loop End Sub |
#2
|
|||
|
|||
Format Color on a Pivot Chart
Perhaps you've beens staring at this too long?
Your Enum is "CustomColors", and your code is looking for "CustomerColors". Just need to change one or the other to match. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "kuhrty" wrote: Hi Everyone. Sorry if this is getting posted twice. I am trying to format colors of the area table. Below is the code for the entire setup and I am running multiple charts. The code below is trying to set collection using an Enum and it is failing. 'ActiveChart.SeriesCollection(1).Points(1).Select 'With Selection.Interior ' .ColorIndex.CustomerColors = PaleOrange 'End With Any help is appreciated Full code below Option Explicit Public i As Integer Public iCtr As Integer Public strQuery As String Public strQuery1 As String Public strQuery2 As String Public strBusType As String Public strBusType1 As String Public strBusType2 As String Public Enum CustomColors PaleOrange = 8183294 'RGB(254, 221, 124) PaleGreen = 8183511 'RGB(215, 222, 124) PaleBlue = 14009007 'RGB(175, 194, 213) PaleViolet = 11173238 'RGB(118, 125, 170) PalePurple = 7486603 'RGB(139, 60, 114) Blue1 = 8281923 'RGB(67, 95, 126) Blue2 = 12029799 'RGB(103, 143, 183) Blue3 = 15984349 'RGB(221, 230, 243) Gray1 = 13354187 'RGB(203, 196, 203) Gray2 = 13354699 'RGB(203, 198, 203) End Enum Public Sub Begin(StrWhatever As String) If StrWhatever = "All" Then iCtr = 2 strQuery1 = "qry_OPRiskBusiness" strQuery2 = "qry_OPRiskEventCategory" strBusType1 = "Business" strBusType2 = "EventCategory" ElseIf StrWhatever = "OP Risk Business" Then iCtr = 1 strQuery1 = "qry_OPRiskBusiness" strBusType1 = "Business" ElseIf StrWhatever = "OP Risk Event Category" Then iCtr = 1 strQuery2 = "qry_OPRiskEventCategory" strBusType2 = "EventCatagory" Else MsgBox "Something is not right", vbOKOnly Exit Sub End If End Sub Public Sub CreatePivotRange() ActiveWorkbook.Names.Add Name:="BusinessPivot", RefersToR1C1:= _ "=OFFSET(qry_OPRiskBusiness!R1C1,0,0,COUNTA(qry_OP RiskBusiness! C1),COUNTA(qry_OPRiskBusiness!R1))" ActiveWorkbook.Names.Add Name:="EventCategoryPivot", RefersToR1C1:= _ "=OFFSET(qry_OPRiskEventCategory!R1C1,0,0,COUN TA (qry_OPRiskEventCategory!C1),COUNTA(qry_OPRiskEven tCategory!R1))" End Sub Public Sub CreateCharts() i = 1 Do While i = iCtr If i = 1 Then strQuery = strQuery1 strBusType = strBusType1 Else strQuery = strQuery2 strBusType = strBusType2 End If If i = 1 Then ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "BusinessPivot").CreatePivotTable TableDestination:= _ "[TestExport_MK.xls]qry_OPRiskBusiness!R1C8", TableName:=strBusType, _ DefaultVersion:=xlPivotTableVersion10 Else ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "EventCategoryPivot").CreatePivotTable TableDestination:= _ "[TestExport_MK.xls]qry_OPRiskEventCategory!R1C8", TableName:=strBusType, _ DefaultVersion:=xlPivotTableVersion10 End If Sheets(strQuery).Select With ActiveSheet.PivotTables(strBusType) .ColumnGrand = False .EnableDrilldown = False .RowGrand = False .SaveData = False .NullString = "0" .RepeatItemsOnEachPrintedPage = False End With ActiveSheet.PivotTables(strBusType).AddFields _ RowFields:=Array("Year", "Quarter"), _ ColumnFields:=strBusType, PageFields:="Region" ActiveSheet.PivotTables(strBusType).PivotFields ("NetAmount_USD1").Orientation = xlDataField Range("H1").Select Charts.Add ActiveChart.Location xlLocationAsNewSheet, strBusType ActiveWorkbook.ShowPivotTableFieldList = False ActiveChart.ChartType = xlAreaStacked ActiveChart.Location Whe=xlLocationAsNewSheet With Selection.Border .Weight = xlHairline .LineStyle = xlAutomatic End With i = i + 1 Loop End Sub Public Sub ChartFormat(strRegion As String) i = 1 Do While i = iCtr If i = 1 Then strQuery = strQuery1 strBusType = strBusType1 Else strQuery = strQuery2 strBusType = strBusType2 End If 'Select the named chart Sheets(strBusType).Select 'Formats the chart 'Formats the charts borders ActiveChart.HasPivotFields = False ActiveChart.PlotArea.Select ActiveChart.Axes(xlValue).MajorGridlines.Select With Selection.Border .ColorIndex = 57 .Weight = xlHairline .LineStyle = xlDot End With 'Formats the Plot Area ActiveChart.PlotArea.Select With Selection.Border .ColorIndex = 16 .Weight = xlThin .LineStyle = xlContinuous End With With Selection.Interior .ColorIndex = 2 .PatternColorIndex = 1 .Pattern = xlSolid End With 'Formats the Legend ActiveChart.Legend.Select With Selection.Border .Weight = xlHairline .LineStyle = xlNone End With Selection.Shadow = False Selection.Interior.ColorIndex = xlAutomatic Selection.AutoScaleFont = True With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 9 End With Selection.Position = xlBottom 'Add the title / With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = strRegion & " Losses By " & strBusType & " (MM)" .HasLegend = True '.Legend.Select 'Selection.Position = xlBottom .Axes(xlValue).Select Selection.TickLabels.NumberFormat = "$#,##0.0" .Axes(xlCategory).Select End With With Selection .MajorTickMark = xlOutside .MinorTickMark = xlNone .TickLabelPosition = xlLow End With 'ActiveChart.SeriesCollection(1).Points(1).Select 'With Selection.Interior ' .ColorIndex.CustomerColors = PaleOrange 'End With i = i + 1 Loop End Sub |
Thread Tools | |
Display Modes | |
|
|