View Single Post
  #2 (permalink)  
Old July 22nd, 2007, 10:35 AM
FullSquat FullSquat is offline
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try the following VBA Code in a procedure. It will position and size your chart, get the values for your chart, set the scale for the y variable, format the gridlines and plot area.


    Dim ws As Worksheet

    Set ws = Sheets("SHIPMENTS")
    ws.ChartObjects.Delete

    Dim co As ChartObject
    Set co = ws.ChartObjects.Add(1, 1, 1, 1)

    'Position and size the chart

    Dim RngToCover As Range
    Set RngToCover = ws.Range(ws.Cells(20, 1), ws.Cells(20 + 20, 9))
    co.Height = RngToCover.Height
    co.Width = RngToCover.Width
    co.Top = RngToCover.Top
    co.Left = RngToCover.Left

    'Chart type line
    co.Chart.ChartType = xlLine


    With co.Chart.SeriesCollection.NewSeries 'Series 1
        .Name = "Shipments"
        .Values = ws.Range("SHIPMENTS") ' Range name on worksheet with Y variable
        .XValues = ws.Range("DATE") ' Range name on worksheet with X variable
    End With

    'Set chart Title, and Y variable Scale


    With co.Chart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Shipments"
        .ChartTitle.Characters.Font.Size = 20
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Dollars"
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Font.Size = 12
        With .Axes(xlValue)
            .MinimumScale = 0
            .MaximumScale = 100000
            .MinorUnit = 1000
            .MajorUnit = 10000
            .MajorTickMark = xlCross
            .MinorTickMark = xlInside
            .TickLabelPosition = xlNextToAxis
        End With
    End With

    'Format gridline and plotArea

    co.Activate

    With co.Chart.Axes(xlValue).MajorGridlines.Border
        .ColorIndex = 57
        .Weight = xlHairline
        .LineStyle = xlDot
    End With

    co.Chart.PlotArea.Interior.ColorIndex = xlNone

If you chart data ranges change, consider making them dynamic ranges.




Reply With Quote