View Full Version : Solved: Pivot Chart Positioning
tccmdr
05-31-2007, 03:41 PM
I have an automated pivot table creation application, as a reporting tool, which is interfaced through a userform.
My issue is:
In two instances, the pivot table created requires a pivot chart. I generate the chart without a problem, but I cannot position it on the worksheet where I would like due to the "Shape" number incrementing each time I run the report:banghead:
Any takers:dunno ??
geekgirlau
05-31-2007, 07:08 PM
Can you assign a name to the chart as you create it?
Brandtrock
05-31-2007, 10:25 PM
Can you post the code?
tccmdr
05-31-2007, 10:50 PM
Here's the code...primitive as it may be:whistle:
Sub BaseChart()
Range("C23").Select
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Result").Range("C23")
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ChartArea.Select
ActiveChart.Location Where:=xlLocationAsObject, Name:="Result"
ActiveSheet.Shapes("Chart 11").IncrementLeft 27.75
ActiveSheet.Shapes("Chart 11").IncrementTop 105#
ActiveSheet.Shapes("Chart 11").ScaleHeight 1.47, msoFalse, _
msoScaleFromBottomRight
ActiveSheet.Shapes("Chart 11").ScaleWidth 1.27, msoFalse, msoScaleFromTopLeft
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).AxisGroup = 2
ActiveChart.ChartArea.Select
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SeriesCollection(1).Select
ActiveChart.HasPivotFields = False
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = False
With Selection.TickLabels.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlX
.Smooth = False
.MarkerSize = 8
.Shadow = False
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=4, _
Degree:=0.231372549019608
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 4
End With
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 70
.HasSeriesLines = False
.VaryByCategories = False
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlCircle
.Smooth = False
.MarkerSize = 8
.Shadow = False
End With
ActiveChart.PlotArea.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.ChartArea.Select
With Selection.Border
.Weight = 2
.LineStyle = 0
End With
Selection.Interior.ColorIndex = xlNone
Sheets("Result").DrawingObjects("Chart 11").RoundedCorners = False
Sheets("Result").DrawingObjects("Chart 11").Shadow = False
ActiveWindow.Visible = False
ActiveSheet.Shapes("Chart 11").IncrementLeft 45
ActiveSheet.Shapes("Chart 11").IncrementTop -42.75
ActiveSheet.Shapes("Chart 11").ScaleWidth 1.14, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 11").ScaleHeight 1.48, msoFalse, msoScaleFromTopLeft
Windows("Damage Model.xls").Activate
Range("A1").Select
End Sub
geekgirlau
05-31-2007, 11:37 PM
Hi Mike,
I don't have time to play with the rest of it at the moment, but you can set the top of the chart to the top left corner of a cell:
ActiveChart.Left = Range("A1").Left
ActiveChart.Top = Range("A1").Top
tccmdr
06-03-2007, 05:53 PM
Hello GG,
Thought you had it:clap: .......then I got this..
Compile Error:
Method or data member not found
Ideas:think:
geekgirlau
06-03-2007, 08:36 PM
This should get you close - when you set the chart object you set the left and top position of the chart based on the left and top of a range, and the width and height (which you may need to play with):
Sub BaseChart()
Dim cht As ChartObject
Set cht = Sheets("Result").ChartObjects.Add( _
Sheets("Result").Range("B1").Left, _
Sheets("Result").Range("B1").Top, 400, 200)
cht.Chart.ChartWizard Source:=Sheets("Result").Range("B23:F25"), _
Gallery:=xlColumn, Format:=6, PlotBy:=xlColumns, _
CategoryLabels:=1, SeriesLabels:=1, HasLegend:=1
With cht.Chart
.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
.Legend.Position = xlBottom
With .ChartGroups(1)
.Overlap = 0
.GapWidth = 70
.HasSeriesLines = False
.VaryByCategories = False
End With
With .PlotArea
.Border.LineStyle = xlNone
.Interior.ColorIndex = xlNone
End With
With .ChartArea
.Border.Weight = 2
.Border.LineStyle = 0
.Interior.ColorIndex = xlNone
End With
With .Axes(xlCategory).TickLabels
.AutoScaleFont = False
.Font.Name = "Arial"
.Font.Size = 8
End With
With .SeriesCollection(1)
.AxisGroup = 2
.HasPivotFields = False
.Border.LineStyle = xlNone
.Shadow = False
.InvertIfNegative = False
.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=4, _
Degree:=0.231372549019608
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 4
End With
With .SeriesCollection(2)
.Border.LineStyle = xlNone
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlCircle
.MarkerSize = 8
End With
End With
Range("A1").Select
End Sub
tccmdr
06-03-2007, 09:53 PM
Thanks GG :friends:
I think the only thing I'll need to do is define a range name and set a dynamic formula for the pivot array in order to capture all variables as follows:
Dim myrge as Range
cht.Chart.ChartWizard Source:=Sheets("Result").Range("myrge")
Insert>>Name>>Define:
myrge=OFFSET('Result'!$B$22,1,0,COUNTA('Result'!$B22:$B1000),3):doh:
geekgirlau
06-04-2007, 12:04 AM
You can record this Mike:
ActiveWorkbook.Names.Add _
Name:="myrge", _
RefersToR1C1:="=OFFSET(Result!R22C2,1,0,COUNTA(Result!R22C2:R1000C2),3)"
tccmdr
06-04-2007, 10:10 PM
That's nailed it:clap:
Thanks GG - I owe you one :beerchug:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.