I know this is a fairly old topic, but I found it extremely relevant to my current project. I work at an industrial manufacturing plant. The owner of the company told me he wants charts (line graphs) to print out at the end of each completed run of material through a machine. There are 6 charts that he wants on one page, and he wants the beginning and end of each to line up perfectly with one another so you can put a straight edge along the page vertically and match up changes to process parameters. I'm logging all the process parameters to a SQL database every 30 seconds and then querying the database by each run number, and that populates the charts. All that stuff was super easy. I chose to use Access to print the charts because its graphics engine is the most customizable that I've found. You can double click on a chart and resize areas by clicking and dragging. You can change font sizes. And best of all, you can use VBA to format the chart.
Like I said, I need the beginning and end of each chart to line up perfectly. The problem I ran into was that the values of each chart are drastically different from one another, AND the max and min values going into each chart can change from one run of material to the next. I've grouped the process parameters together into 6 charts because those items normally have similar max and min values, but every once in a while one of the parameters spikes WAY off the chart. Well, if the max value of the Y axis is set to Auto, it skews the entire chart and the other parameters are impossible to read, and more importantly, the numbers for the Y axis might change from 10's, to 100's, shifting the start of the chart over to the right a few pixels. So at first it appeared as though my options were to a) manually set a max value and risk having some of my data points go off the chart, and have the values of the lines less readable due to loss of resolution, or b) let Access set the max value automatically and risk having one bad value skew the chart.
What I was looking to do was set a minimum max value. If inlet temperature is less than 100, then set the max value of Y to 100. But if inlet temperature is greater than 100, set the max Y value to whatever the inlet temperature is. In the setup windows for the charts, this is not possible. But thanks to the code posted on this forum, I was able to modify it to fit my needs and accomplish my goal. Here's my code.
Private Sub Form_Activate()
Dim objChart1 As Object
Dim objAxis1 As Object
Set objChart1 = Me.gphTemperatures.Object
If Me.MaxOfInletTemp < 100 Then
Set objAxis1 = objChart1.Axes(2)
objAxis1.MaximumScale = 100
ElseIf Me.MaxOfInletTemp > 100 Then
Set objAxis1 = objChart1.Axes(2)
objAxis1.MaximumScale = Me.MaxOfInletTemp
End If
End Sub
And vwala, it works.
