Can I programmatically control the Y axis major and minor units on
charts? The ones that Access is deciding for itself are wrong.

Thanks,
Laura

Hi Laura,

You certainly can programmatically control all aspects of charts via VBA.
The main problem is figuring out the object model; I use an old VBA Help
file for Graph8, available from http://support.microsoft.com/kb/162883 It's
for Office97, but most of it seems applicable still, and I haven't bothered
to search for a later version.

As an example, here's some code I use in one of my applications to force the
y-axes scales (the chart has two y-axes) to be 1, 2 or 5 to the apppropriate
power of ten. I call this routine in the Form_Open event, but it could be
called from other events as required.

Private Sub RescaleChart()
'Last edited: Rob Parker, 24 Sep 2009
Dim strSQL As String
Dim strDivFilter As String
Dim dblMaxHours As Double
Dim dblMaxCost As Double
Dim dblMaxHoursScale As Double
Dim dblMaxCostScale As Double
Dim rst As DAO.Recordset

'get maximum values for re-scale - will always be in first record
strSQL = Me.chtEffort.RowSource
Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
dblMaxHours = Nz(rst!Hours, 0)
dblMaxCost = Nz(rst![Cost OH], 0)
rst.Close
Set rst = Nothing

'Rescale the y-axes
'get sensible scale values
dblMaxHoursScale = OneTwoFive(dblMaxHours)
dblMaxCostScale = OneTwoFive(dblMaxCost)
'set values for primary y-axis
If dblMaxCostScale <> 0 Then
Me.Controls("chtEffort").axes(2, 1).maximumscale = dblMaxCostScale
Me.Controls("chtEffort").axes(2, 1).majorunit = dblMaxCostScale / 5
End If
If dblMaxHoursScale <> 0 Then
Me.Controls("chtEffort").axes(2, 2).maximumscale = dblMaxHoursScale
Me.Controls("chtEffort").axes(2, 2).majorunit = dblMaxHoursScale / 5
End If
End Sub

This code also requires a function OneTwoFive, which I have in a standard
module.

Public Function OneTwoFive(X As Variant) As Variant
' Author : Rob Parker, 23 Sep 2009
' Last Edit : Rob Parker, 24 Sep 2009
' Purpose : Return a number in set 1,2,5, 10, 20, 50 ... greater than
input
' : Return negative number if input is negative
' : Use for setting optimised scale on charts
' : Based on code from www.freebasic.net/forum/
'---------------------------------------------------------------------------------------
Dim sngScale As Single
Dim y As Single
Dim blnNegative As Boolean

On Error GoTo OneTwoFive_Error

If IsNumeric(X) Then
If X = 0 Then 'special case - return 0 for input 0
OneTwoFive = 0
Exit Function
ElseIf X < 0 Then
blnNegative = True
X = X * -1
End If
sngScale = Int(Log(X) / Log(10)) 'Int(Log10(x))
Select Case X / (10 ^ sngScale)
Case 0 To 2
y = 2
Case 2 To 5
y = 5
Case 5 To 10
y = 10
Case Else
MsgBox "Illegal data in Function OneTwoFive"
End Select
OneTwoFive = y * (10 ^ sngScale)
If blnNegative Then OneTwoFive = OneTwoFive * -1
Else
OneTwoFive = Null
End If

ExitPoint:
Exit Function

OneTwoFive_Error:
Select Case Err.Number
Case 5 'Illegal argument
OneTwoFive = Null
Resume ExitPoint
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
OneTwoFive of Module modFunctionLibrary"
End Select
Resume ExitPoint
Resume 'Allows control-break to enter debug, and find statement causing
error
End Function


Feel free to adapt as necessary.

HTH,

Rob


"musicloverlch" <lhowey (AT) gmail (DOT) com> wrote

THANK YOU! THANK YOU! THANK YOU!

On Jun 1, 11:36*pm, "Rob Parker" <NO robpparker SPAM @ FOR
optusnet.com.au ME> wrote:

On 6/2/2011 12:36 AM, Rob Parker wrote:
There is a later version for Access 2003 at:

http://www.microsoft.com/downloads/e...ang=en&pf=true

Steve

Thanks, Steve

Rob

"Sky" <Sky (AT) NoSpam (DOT) com> wrote