Resizing an Excel Chart from VB

Posted on 2004-11-19
Last Modified: 2012-08-13
I have found out how to add a chart to an excel file but what I want to do is move the chart  and resize it, can anybody point me in the right direction?

Here is how I am adding the chart

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlSheet2 As Excel.Worksheet
    Dim xlChart As Excel.Chart
    Dim i As Integer
    Dim NextVal As Integer
    ' Create Excel Application object
    Set xlApp = New Excel.Application
    ' Create a new workbook
    Set xlBook = xlApp.Workbooks.Add
    ' Get rid of all but one worksheet
    xlApp.DisplayAlerts = False
    For i = xlBook.Worksheets.Count To 2 Step -1
    Next i
    xlApp.DisplayAlerts = True
    Set xlSheet2 = xlBook.ActiveSheet
    xlSheet2.Name = "mysheet"
    Set xlChart = xlApp.Charts.Add
    With xlChart
        Dim xlsSerie As Excel.SeriesCollection
        Set xlsSerie = .SeriesCollection
        Dim xlsAxisCategory As Excel.Axes
        .ChartType = xlColumnStacked
        .SetSourceData xlSheet2.Range("B1:B" & j)
        xlsSerie.Item(1).HasDataLabels = True
        Set xlsAxisCategory = .Axes(, xlPrimary)
        xlsAxisCategory.Item(xlCategory).HasTitle = True
        xlsAxisCategory.Item(xlCategory).AxisTitle.Characters.Text = "Hours"
        .PlotBy = xlColumns
        .Location xlLocationAsObject, conSheetName2
    End With
    With xlBook.ActiveChart
        .HasTitle = True
        .HasLegend = True
        .SeriesCollection(1).XValues = xlSheet2.Range("A1:A" & j)
        With .ChartTitle
            .Characters.Text = conSheetName2 & " Chart"
            .Font.Size = 12
            .Shadow = True
            .Border.LineStyle = xlSolid
        End With
        With .ChartGroups(1)
            .GapWidth = 14
            .VaryByCategories = True
        End With
    End With

Thanks in advance
Question by:cdthurman
    LVL 26

    Expert Comment

    From record the macro

    ' move
    ActiveSheet.Shapes("Chart 1").IncrementLeft -95.4
    ActiveSheet.Shapes("Chart 1").IncrementTop -94.2
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.51, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleHeight 1.39, msoFalse, msoScaleFromTopLeft
    LVL 5

    Accepted Solution

    I'm getting errors from your code - j is not set and neither is conSheetName2

    Assuming you want the chart loose on mysheet then change your location line a little

            .Location Where:=xlLocationAsObject, Name:=xlSheet2.Name

    The position of the chart is handled by the ChartObject (the container for the chart itself)

        With xlSheet2.ChartObjects(1)
            .Left = 40
            .Top = 40
        End With


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now