Charting Macro Help

Posted on 2013-09-18
Medium Priority
Last Modified: 2013-09-19
can someone help? trying to figure out a way to create a macro that takes data from column A and column B and creates a chart.

Any ideas on the code to do this?

Question by:eastsidemarket
LVL 18

Accepted Solution

Steven Harris earned 2000 total points
ID: 39504341
Here is one example from Peltier Technical Services, Inc..  It will prompt you for the range to put the chart and a range to select for the data.

Option Explicit

Sub CreateChart()
  Dim objChart As ChartObject
  Dim myChtRange As Range
  Dim myDataRange As Range
  With ActiveSheet
    ' What range should chart cover
    Set myChtRange = Application.InputBox( _
        prompt:="Select a range where the chart should appear.", _
        title:="Select Chart Position", Type:=8)
    ' What range contains data for chart
    Set myDataRange = Application.InputBox( _
        prompt:="Select a range containing the chart data.", _
        title:="Select Chart Data", Type:=8)
    ' Cover chart range with chart
    Set objChart = .ChartObjects.Add( _
        Left:=myChtRange.Left, Top:=myChtRange.Top, _
        Width:=myChtRange.Width, Height:=myChtRange.Height)
    ' Put all the right stuff in the chart
    With objChart.Chart
        .ChartArea.AutoScaleFont = False
        .ChartType = xlXYScatterLines
        .SetSourceData Source:=myDataRange
        .HasTitle = True
        .ChartTitle.Characters.text = "My Title"
        .ChartTitle.Font.Bold = True
        .ChartTitle.Font.Size = 12
        With .Axes(xlCategory, xlPrimary)
            .HasTitle = True
            With .AxisTitle
                .Characters.text = "My X Axis"
                .Font.Size = 10
                .Font.Bold = True
            End With
        End With
        With .Axes(xlValue, xlPrimary)
            .HasTitle = True
            With .AxisTitle
                .Characters.text = "My Y Axis"
                .Font.Size = 10
                .Font.Bold = True
            End With
        End With
    End With
  End With
End Sub

Open in new window


Author Closing Comment

ID: 39506870
useful thanks!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

597 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