Huge font when creating graphs with VBA

[learning2fly]

I have written a piece of VBA code to automatically generate graphs from a page of data. The code seems to run fine most of the time but occasionally I get a problem with huge font on the X and Y axes. I don't really understand why excel does this as most of the time it runs okay. I think it might be something to do with memory but I am not that experienced in that kind of thing.

Can anyone help me out?

Many Thanks


I have attached the code that generates the graphs below:

'Get column width and row height from Graph sheet
cw = Sheets(Graph_Sheet).Columns(1).Width
rh = Sheets(Graph_Sheet).Rows(1).Height

intChartHeight = intChartHeightRows * rh
intChartWidth = intChartWidthColumns * cw

For n = 0 To NumberOfGraphs - 1

'Add comparison Graph

Set MyCurrentChart = Sheets(Graph_Sheet).ChartObjects.Add(0.5 * cw, rh + intChartHeight * n, intChartWidth, intChartHeight)

MyCurrentChart.Name = "Graph" + Str

Start_Row_Num = DataStartRow + RowsPerGraph * n
End_Row_Num = DataStartRow + RowsPerGraph - 1 + RowsPerGraph * n

'Use first column to get title for charts (expected date in this column)
Chart_Date = Sheets(Data_Sheet).Cells(Start_Row_Num, 1)
'Chart_DayType = Sheets(Data_Sheet).Cells(Start_Row_Num, 3)

'Set chart type
MyCurrentChart.Chart.ChartType = xlLineMarkers

'Add data from each column to graph

For p = 1 To ColsToGraph

MyCurrentChart.Chart.SeriesCollection.Add _
Source:=Range(Sheets(Data_Sheet).Cells(Start_Row_Num, GraphCol(p)), Sheets(Data_Sheet).Cells(End_Row_Num, GraphCol(p)))

MyCurrentChart.Chart.SeriesCollection(p).Name = Sheets(Data_Sheet).Cells(HeadingRow, GraphCol(p))

Next


'Set XY Range
XYRange = Range(Sheets(Data_Sheet).Cells(Start_Row_Num, XYCol1), Sheets(Data_Sheet).Cells(End_Row_Num, XYCol1))
'MyCurrentChart.Chart.SeriesCollection(1).XValues = XYRange

'Format Legend
MyCurrentChart.Chart.HasLegend = True
With MyCurrentChart.Chart.Legend
.Font.Name = "Arial"
.Font.FontStyle = "Regular"
.Font.Size = intLegendFont
.Position = xlBottom
End With

'Format Title
MyCurrentChart.Chart.HasTitle = True

Dim varCaption As Variant

'Check if title column set
If (TitleColumn = "&quot Then
varCaption = Graph + Format(n + 1)
Else
'Title caption taken from title column of first row of set
varCaption = Format(Sheets(Data_Sheet).Cells(Start_Row_Num, TitleColumn), "dddd dd of mmmm yyyy&quot
End If

'Add title
With MyCurrentChart.Chart.ChartTitle
.Caption = varCaption
.Font.Size = intTitleFont
End With

 

[danomaniac]

learning2fly,

You might try this..

'this is for the X axis -

ActiveChart.Axes(xlCategory).TickLabels.AutoScaleFont = False

Or you can add this to set more properties...

ActiveSheet.ChartObjects("Chart 1&quot.Activate
ActiveChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With Selection.TickLabels.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

Everything is the same for the Y axis except:
ActiveChart.Axes(xlValue).TickLabels.AutoScaleFont = False

Hope this helps,

Dan

"It's more like it is now, than it ever has been."