Solved: Pivot Chart Positioning

[tccmdr]

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]

Can you assign a name to the chart as you create it?

[Brandtrock]

Can you post the code?

[tccmdr]

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]

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]

Hello GG,


Thought you had it:clap: .......then I got this..




Compile Error:

Method or data member not found


Ideas:think:

[geekgirlau]

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]

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]

You can record this Mike:


ActiveWorkbook.Names.Add _
Name:="myrge", _
RefersToR1C1:="=OFFSET(Result!R22C2,1,0,COUNTA(Result!R22C2:R1000C2),3)"

[tccmdr]

That's nailed it:clap:

Thanks GG - I owe you one :beerchug: