Excel chart that has min/max values based on cell data

**Buster1320** · 09-27-2009, 12:39 AM

I am trying to build a chart that has the x/y min/max values tied directly to cell values. I located code that will allow this, but I am having difficulties applying it and placing it correctly.

My desire to have hours (and date due to capability to handle the values when it crosses a date), but I want the hours in even hours (ie 0100, 1500, etc). I can get the worksheet to calculate the values that I want for min/max, but I don't even know how to begin to get that spread into an interval that equate to even hours on the axis.

I don't even know if want I want to accomplish is possible, but I have attached my work in progress that explains what I am trying to complete. I think that my cell notes explain what the values are....

Thanks for all of the help!
Buster

**Andy Pope** · 09-27-2009, 06:58 AM

This will update the chart, except for the MajorUnits as these are currently not calculated

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("A4:B14"), Target) Is Nothing Then
        With Sheets("Chart")
            With .Axes(xlCategory)
                .MaximumScale = Range("$F$22").Value
                .MinimumScale = Range("$F$21").Value
'                .MajorUnit = Range("$E$4").Value
            End With
            With .Axes(xlValue)
                .MaximumScale = Range("$D$24").Value
                .MinimumScale = Range("$D$23").Value
'                .MajorUnit = Range("$E$24").Value
            End With
        End With
    End If
End Sub

**Buster1320** · 09-28-2009, 10:51 PM

Andy,

That works great! Thanks for the help...

I figured out a way for it to calculate the major units with date/time, probably did it a longer/harder way than it had to be done.... but it does what I want it to do! At least at this stage...

Buster