Scaling a Chart Axis

[Paul H]

I have two, nearly identical charts.

The X axis on Chart 1 is spread over a date/time range.
Example: 09/01 12 am, 09/01 8 am, 09/01 4 pm, 09/02 12 am etc.
This chart automatically changes it's major tick marks depending on the range selected. One week has a tick mark and label for each day, one Month has a tick mark for each week, 6 months has a tick mark for each month.

The axis on Chart 2 is spread over a formatted text field combining date and shift.
Example: 09/01 Shift A, 09/01 Shift B, 09/01 Shift C, 09/02 Shift A etc.

This chart does not automatically adjust tick marks and labels which results in an illegible blur when choosing a large date range.



This charts has a different set of options.

• Number of categories between tick mark labels
• Number of categories between tick

I've determined I have to come up with a formula to change these via code. I can come up with the formula, but I can't find the property I need to set. Can you point me towards these properties or come up with something much simpler that I haven't thought of.

Followup note. Chart 1 is an X/Y scatter chart. Chart 2 is a line chart. When I convert it to an XY Scatter chart the scale adjusts automatically, but the labels on the X axis change to row numbers.

[June7]

Here is example code behind a report in my db setting y axis scale and units for a line graph:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Plot Vibratory graph
Dim MinDD As Double
Dim MaxDD As Double
MinDD = DMin("Den", "GraphVibratory")
MaxDD = DMax("Den", "GraphVibratory")
With Me.gphDensity
If MinDD > 0 Then
    .Axes(xlValue).MinimumScale = MinDD
    If Me!Metric = True Then
        MaxDD = Int(MaxDD / 100) * 100 + 100
        MinDD = MaxDD - 1000
        .Axes(xlValue).MaximumScale = MinDD
        .Axes(xlValue).MinimumScale = MinDD
        .Axes(xlValue).MajorUnit = 200
        .Axes(xlValue).MinorUnit = 40
    Else
        MaxDD = Int(MaxDD / 5) * 5 + 5
        MinDD = MaxDD - 50
        .Axes(xlValue).MaximumScale = MaxDD
        .Axes(xlValue).MinimumScale = MinDD
        .Axes(xlValue).MajorUnit = 10
        .Axes(xlValue).MinorUnit = 2
    End If
    .Axes(xlValue, xlPrimary).HasTitle = True
    If Me!Metric = True Then
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Max. Dry Density, Kg/cu.m"
    End If
    .Axes(xlCategory, xlPrimary).HasTitle = True
    If Me!Metric = True Then
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Percent Passing 4.75 mm Sieve"
    End If
End If
End With
End Sub

[Paul H]

I have something very much like that for my Y axis, but that does not work on line chart X axis.

[June7]

Don't think can program the X axis on line chart. I think I have for X/Y Scatter. Don't have that file with me. Remind me on Wednesday if I don't get back before then.

[Paul H]

I hate taking no for an answer, but this has dropped to the level of curiosity at this point.

Have a good weekend. See you Wednesday.

Paul

[June7]

This is excerpt of my code for an XY Scatter that sets both axes.
.Axes(xlCategory).MinimumScale = MinAC
.Axes(xlCategory).MaximumScale = MaxAC
.Axes(xlValue).MinimumScale = MinUWT
.Axes(xlValue).MaximumScale = MaxUWT
.Axes(xlValue).MinorUnit = MnrUWT
.Axes(xlValue).MajorUnit = MajUWT
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Text = "Unit Weight, " & IIf(booMetric = True, "kg/cu.m", "pcf")