Graphs in VBA

[kathleen]

I have two graphs that are created through vba with properties and location being set. For some reason it randomly changes the font size and location. Does anyone have any ideal how this could be happening?

Surely appreciate any help I can get!

 

[shades]

Howdy. It would help if you could post the code (be sure to include the code tags).

 

[kathleen]

This graph is selected much earlier in the code and deleted, so it can be added back here. The chart should start in cell a4.

'--------------------------------------------------------------------------------------------
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("By_Account").Range("A33:F35"), _
PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="By_Account"
ActiveChart.HasDataTable = False
ActiveChart.Parent.Name = "TopFive"

'Set plot area to white
'--------------------------------------------------------------------------------------------
ActiveChart.Axes(xlValue).MajorGridlines.Select
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

'Move and size graph
'--------------------------------------------------------------------------------------------
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("TopFive").IncrementLeft -183.75
ActiveSheet.Shapes("TopFive").IncrementTop -279.75
ActiveSheet.Shapes("TopFive").ScaleWidth 1.34, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("TopFive").ScaleHeight 1.82, msoFalse, msoScaleFromTopLeft

'Change Plot Point Color
'--------------------------------------------------------------------------------------------
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With

'Format Axis
'--------------------------------------------------------------------------------------------
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.TickLabels.NumberFormat = "#,##0_);[Red](#,##0)"
Windows("Budget Package.xls").SmallScroll Down:=6
ActiveChart.SeriesCollection(1).Select
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

'Format 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 = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Position = xlTop

'Insert Chart Titles
'--------------------------------------------------------------------------------------------
ActiveChart.ChartArea.Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Top Five Accounts"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Dollars"
End With

ActiveChart.Legend.Select
Selection.Left = 360
Selection.Top = 12
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
Selection.Top = 30
Selection.Height = 323
Selection.Top = 23
Selection.Height = 330

Selection.Height = 338
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("TopFive").ScaleHeight 1.01, msoFalse, msoScaleFromTopLeft
ActiveWindow.Visible = False

ActiveSheet.Shapes("TopFive").ScaleHeight 1.08, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("TopFive").ScaleWidth 1#, msoFalse, msoScaleFromTopLeft
ActiveWindow.Visible = False
Windows("Budget Package.xls").Activate

'Insert Column to break top 5 accounts from other accounts
'--------------------------------------------------------------------------------------------
Windows("Budget Package.xls").Activate
Columns("G:G").Select
Range("G7").Activate
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 5
Selection.Clear
ActiveSheet.ChartObjects("TopFive").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("TopFive").ScaleWidth 1#, msoFalse, msoScaleFromTopLeft

 

[kathleen]

Does anyone have any ideal how to make the graphs always appear in the same place?

 

[dtaylor]

Hi There -

this should do it for you

With ActiveChart
.Parent.Top = Sheets("OverView and Analysis").Range("J75").Top
.Parent.Left = Sheets("OverView and Analysis").Range("J75").Left
.Parent.Height = Sheets("OverView and Analysis").Range("J75:Q89").Height
.Parent.Width = Sheets("OverView and Analysis").Range("J75:Q89").Width
End With

hope it helps - works for me in my apps!

Dan

 

[kathleen]

Thank you will go and try it now.

 

[kathleen]

Thank you! You all are the simply the best! Think I can go listen to Tina Turner now!