Format Color on a Pivot Chart

  • Thread starter Thread starter kuhrty
  • Start date Start date
K

kuhrty

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_OPRiskBusiness!
C1),COUNTA(qry_OPRiskBusiness!R1))"

ActiveWorkbook.Names.Add Name:="EventCategoryPivot",
RefersToR1C1:= _
"=OFFSET(qry_OPRiskEventCategory!R1C1,0,0,COUNTA
(qry_OPRiskEventCategory!C1),COUNTA(qry_OPRiskEventCategory!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:=xlDatabase,
SourceData:= _
"BusinessPivot").CreatePivotTable TableDestination:= _
"[TestExport_MK.xls]qry_OPRiskBusiness!R1C8",
TableName:=strBusType, _
DefaultVersion:=xlPivotTableVersion10
Else
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
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 Where:=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
 
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 said:
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_OPRiskBusiness!
C1),COUNTA(qry_OPRiskBusiness!R1))"

ActiveWorkbook.Names.Add Name:="EventCategoryPivot",
RefersToR1C1:= _
"=OFFSET(qry_OPRiskEventCategory!R1C1,0,0,COUNTA
(qry_OPRiskEventCategory!C1),COUNTA(qry_OPRiskEventCategory!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:=xlDatabase,
SourceData:= _
"BusinessPivot").CreatePivotTable TableDestination:= _
"[TestExport_MK.xls]qry_OPRiskBusiness!R1C8",
TableName:=strBusType, _
DefaultVersion:=xlPivotTableVersion10
Else
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
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 Where:=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
Click to expand...
 
Back
Top