Modifying charts with VBA (multi-axis)

[emorris1000]

Howdy all, 2 posts in one day, so you know I'm having fun.

I have a bunch of charts in my forms, and my boss wants to be able to adjust axes. I have added a context menu that brings up a little form and takes values that are then thrown into the graph by way of a function.

I've copied some stuff I found online here:

http://www.access-programmers.co.uk/forums/showthread.php?t=48641&highlight=Maximum+Scale


Here's the current code:

Public Function AdjustTREFAxis()
    'See form fTREFaxis
    Dim objChart1 As Object
    Dim objAxis1 As Object
 
    DoCmd.OpenForm "fTREFAxis"
 
    While IsOpen("fTREFAxis")
        DoEvents
    Wend
 
    Set objChart1 = Forms![TGeneralChar]![SubTREFData].Form.Graph19.Object
 
    'adjust x axis
 
    Set objAxis1 = objChart1.Axes(1)
 
    objAxis1.MinimumScale = TransferVar1  'TMin
    objAxis1.MaximumScale = TransferVar2  'Tmax
 
    'adjust y1 axis
 
    Set objAxis1 = objChart1.Axes(2)
 
    objAxis1.MinimumScale = TransferVar3  'ConcMin
    objAxis1.MaximumScale = TransferVar4  'ConcMax
 
    'adjust y2 axis, this doesn't work (Axes(3) doesn't seem to exist)
'
'    Set objAxis1 = objChart1.Axes(3)
'
'    objAxis1.MinimumScale = TransferVar5  'CumMin
'    objAxis1.MaximumScale = TransferVar6  'CumMax
 
    ClearGlobalVars
 
End Function

notes:
-ignore the global vars, they are a temporary measure.
-"Graph19" I know, not good. I never expected to call it in code though , I'll fix it later
-the while loop keeps the rest of the code from running until the axes have been submitted.

Ok, so this code works for the x and y1 axes. I assumed that Axes(3) would iterate to the Y2 axis, it does not

I also found that after you change the axis the changes are persistent for the graph even upon repaint/requerying it. This is problematic and I need a way to set an On Load event that resets the graph to "autoscale"

So, what I really need is some decent documentation on this stuff, but I get the idea that since I am using generic object variables that may be tricky.

Lacking that, if someone knows how to access the Y2 axis, or if someone knows how to reset the axes to autoscale that would be keen.

Or, failing all that, if someone knows a good resource for graph management in access it would be appreciated.

ed: Actually could someone move this to the Modules/VBA section? Would probably work better there.

 

[emorris1000]

OK. So I found the object model for this under an old VB model and was happy to see that it works here as well. To select a secondary axis you use:

.axes(2,xlsecondary)

The 2 means "y". so for a secondary x-axis you would use .axes(1,xlsecondary)

Now, what this doesn't do is tell me how to go back to the autoscaling, which I need because after updating this the axis number sticks.

ed: Ok so I found some good documentation here:

http://msdn.microsoft.com/en-us/library/office/aa208306(v=office.11).aspx

Now, based on this I should be able to use the "MinimumScaleIsAuto" property. I've tried that and gotten a run--time error 1004: Unable to set the "minimumScaleIsAuto property of the axis class.

Any thoughts?

--------------------------
Edit2:

OK, SOLVED! (at 5:00 even!)

Basically the thing was failing because I set it as an "on load" event. The problem is that on load the form is empty. Trying to grab the axes on load ends up failing because there is no graph.

I went ahead and added a "Reset Axes" as an option on the context menu for the graph. This sets the MinimumScaleIsAuto property (or MaxScale...)

WORKS LIKE A CHARM.

I'M GOING HOME TO GO HAVE A BEER.