A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Format Color on a Pivot Chart



 
 
Thread Tools Display Modes
  #1  
Old September 23rd, 2009, 10:24 PM posted to microsoft.public.excel.charting
kuhrty
external usenet poster
 
Posts: 6
Default 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  
Old September 24th, 2009, 03:08 PM posted to microsoft.public.excel.charting
Luke M
external usenet poster
 
Posts: 2,672
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


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


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