Sizing ChartArea MSGraph Control

[SA Brisbane]

Using Access 2010 on Windows 8.1. I have a subform that has 18 small MSGraph objects on it. I am requerying each chart when the user selects different filter options. The charts are very simple (no legends, no titles, 3 columns on each, number format in %) the MSGraph controls are all the same height and width. I have am using a public sub to try to control some of the formatting eg font size and style for ticklabels and colour of the columns (which all seems to be working) but despite this consistency, the graphs all render different sizes failing to "Stretch" to fill the MSGraph control. The tickLabels are also different sizes despite turning Autoscaling off and setting a font size. I can't set the chartArea.Height or width. At this stage I would settle for them all to be the same size - What am I missing? I am not a programmer just a problem solver so would appreciate any help I can get Thanks

Public Sub GreatFormats(EBSArea As String, GrNumber As Integer, SFrmName As String, FrmName As String)
Dim GrphName As String
Dim GrpType As String 'Status or Priority Graph

Const twips As Long = 1440
Dim MySForm As Form
Set MySForm = Forms(FrmName).Controls(SFrmName).Form
For I = 1 To GrNumber
GrphName = "Gr" & EBSArea & I
GrphType = MySForm(GrphName).Tag
MySForm(GrphName).SizeMode = acOLESizeClip
'MySForm(GrphName).Width = 2 * twips
'MySForm(GrphName).Height = 1.5 * twips

With MySForm(GrphName).Object ' Format each chart

.HasTitle = False 'No Title
.HasLegend = False
.DisplayBlanksAs = xlNotPlotted
.ChartGroups(1).Overlap = -25 'place a gap between the bars
.ChartGroups(1).GapWidth = 126 'centre the bars on the x-axis

With .Axes(xlValue) ' Scale and format y-axis
.HasTitle = False
.MaximumScale = 1
.MinimumScale = 0
.MajorUnit = 0.25

.TickLabelPosition = xlTickLabelPositionNextToAxis
.TickLabels.NumberFormat = "0%"
.TickLabels.Font.Name = "Calibri"
.TickLabels.Font.Size = 10
.TickLabels.Font.FontStyle = "Regular"
.TickLabels.AutoScaleFont = False

End With

With .Axes(xlCategory) ' Scale and format y-axis
.HasTitle = False
.TickLabelPosition = xlNone

End With

For X = 1 To .SeriesCollection.Count 'cycle through the series

With .SeriesCollection(X)
.HasDataLabels = True
.ApplyDataLabels Type:=xlValue
.DataLabels.Font.Name = "Calibri"
.DataLabels.Font.Size = 8
.DataLabels.Font.FontStyle = "Regular"
.DataLabels.AutoScaleFont = False


Select Case X & GrphType
Case "1Priority"
.Interior.Color = (RGB(205, 0, 33))
Case "2Priority"
.Interior.Color = (RGB(254, 175, 22))
Case "3Priority"
.Interior.Color = (RGB(16, 31, 102))
Case "1Status"
.Interior.Color = (RGB(149, 149, 149))
Case "2Status"
.Interior.Color = (RGB(0, 125, 0))
Case "3Status"
.Interior.Color = (RGB(148, 0, 141))
End Select
End With
Next X



.Application.Update
End With
Next I
End Sub

 

[JHB]

Could you show a printscreen of what you get and what you want, then it could be easier to understand your problem?

 

[SA Brisbane]

OK I have attached a screen shot of my subform in Form View. You can see that the graphs are all different sizes as well as some of the fonts. I would like them all to be the same size and fill their MSgraph containers. They are pretty simple and very consistent .ie always percentages and always three columns. Thanks for your response

 

[JHB]

Have you tried to make one graph as master, and then copy/paste it, and afterwards change the rowsource?

 

[SA Brisbane]

Thanks for your suggestion. I have methodically copied and pasted a master chart and have reset the row source for each. So far so good! I appreciate your time.