Use VBA to make a chart using a column selected at the start of the macro

**mineralfellow** · 06-01-2009, 09:06 PM

I have a workbook with several hundred worksheets, and there are several columns of data on each sheet. I am trying to graph some of these columns against one another. For instance, I want to graph Depth against Grain size and Lithology on the same graph for all of the sheets. I have figured out how to add a series to a graph, but there are a few things I haven't figured out:
I would like to be able to select which column to plot at the start of the macro.
I would like to be able to select what range of sheets, or all sheets, that will have data plotted on the chart at the start of the macro (or at least be able to run through all worksheets).

Any help here is greatly appreciated!

My code so far:

Code:

Sub add_chart()
Dim sourceSheet As Worksheet, sourceName As String, i As Long, targetSheet As Chart, Current As Worksheet


Set sourceSheet = Application.ActiveWorkbook.ActiveSheet
    sourceName = sourceSheet.Name
Charts.Add
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlLine 'Type of graph
ActiveChart.SetSourceData Source:=Sheets(sourceName).Range("B2:B124"), PlotBy _
:=xlColumns 'data source
ActiveChart.SeriesCollection(1).XValues = Sheets(sourceName).Range("a2:A124") 'naming the x-axis
ActiveChart.SeriesCollection(1).Name = "Depth vs Lithology" ' Heading of the graph

Set targetSheet = Application.ActiveWorkbook.ActiveChart
sourceSheet.Activate

'Tests how much data is on the sheet 
i = 2
Range("A2").Select
While ActiveCell.Value <> Empty
Range("A" & CStr(i)).Select
i = i + 1
Wend
i = i - 3
targetSheet.Activate
With ActiveChart.SeriesCollection.NewSeries
.Name = sourceSheet.Range("F1")
.Values = sourceSheet.Range("F2:F" & i)
.XValues = sourceSheet.Range("A2:A" & i)
End With


With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.PlotArea.Select ' Background of graph
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Interior.ColorIndex = xlNone



End Sub

**Andy Pope** · 06-02-2009, 06:19 AM

Is the layout on each sheet the same, even if the number of rows varies?

Can you post small example of data layout and type of chart expected.

**mineralfellow** · 06-02-2009, 11:22 AM

This has two sheets with my data, and two charts made from this program. What I would like is the ability to plot multiple sheets on 1 graph (and I have over 100 sheets with this sort of info) and be able to declare which heading I would like to plot.

Yes, the number of columns is constant, but the number of rows varies.

sample_needsgraphs.xls

**Andy Pope** · 06-02-2009, 11:34 AM

To confirm.

You specify a column, such as MaxGrain, and the chart should contain a line for each MaxGrain for all Sample sheets?

**mineralfellow** · 06-02-2009, 11:53 AM

Yes, or alternately specify a set of sheets to compare to one another.

**Andy Pope** · 06-02-2009, 12:24 PM

try this, the macro Main displays a userform for data choice.

**mineralfellow** · 06-02-2009, 12:54 PM

Wow, that's pretty incredible. I haven't worked with forms before, so I'm not clear on what all of the code means. There is a slight glitch when I copy this over to my main workbook. While it still functions correctly, the names in the "Select Column" field do not appear, which also means that the name at the top of the chart is incomplete "Depth vs"...

Does that mean I didn't copy something correctly, or does a line need to be changed?

Thanks for the help!

**mineralfellow** · 06-02-2009, 03:03 PM

Problem was that the form looks at the first worksheet in the workbook. I had a different sheet as the first one. Problem solved.

Next thing I would like to do is pick a range of depths to plot from each worksheet, but that probably requires a new thread.

**mineralfellow** · 06-02-2009, 08:24 PM

Ok, I am having another problem. I'm trying to plot agaist a min grain size instaed of depth, and it is returning a problem on the line:

Code:

                 .XValues = rngXData

To attempt to get it to plot min grain size, I changed this line to have 5 instead of 1:

Code:

            Set rngXData = Intersect(shtTemp.UsedRange, shtTemp.Columns(5))

When this did work, it gave the graph in point space rather than min grain size space. Any idea what to do?

**Andy Pope** · 06-03-2009, 06:18 AM

The line you changed is for defining the x axis values, Depth.

Does the sheet have depth values in column A?

**mineralfellow** · 06-03-2009, 12:23 PM

Depth is in column A. What I want to do is plot what is in column 5 across the X axis. How do I modify this code to do that?

**Andy Pope** · 06-04-2009, 06:07 AM

You just need to swap around the X and Y range variables.

Code:

Sub CreateChart()

    Dim chtTemp As Chart
    Dim rngYData As Range
    Dim rngXData As Range
    Dim lngIndex As Long
    Dim lngDataOffset As Long
    Dim strName As String
    Dim shtTemp As Worksheet
    
    Set chtTemp = Charts.Add
    Do While chtTemp.SeriesCollection.Count > 0
        chtTemp.SeriesCollection(1).Delete
    Loop
    
    lngDataOffset = ListBox1.ListIndex + 1
    For lngIndex = 0 To ListBox2.ListCount - 1
        If ListBox2.Selected(lngIndex) Then
            Set shtTemp = Worksheets(ListBox2.List(lngIndex))
            Set rngYData = Intersect(shtTemp.UsedRange, shtTemp.Columns(1))
            Set rngYData = rngYData.Offset(1, 0).Resize(rngYData.Rows.Count - 1, 1)
            Set rngXData = rngYData.Offset(0, lngDataOffset)
            With chtTemp.SeriesCollection.NewSeries
                .Values = rngYData
                .XValues = rngXData
                .Name = shtTemp.Name
                .ChartType = xlXYScatterLinesNoMarkers
            End With
        End If
    Next
        
    With chtTemp
        .HasTitle = True
        .ChartTitle.Text = "Depth vs " & ListBox1.Value
        With .Axes(xlValue)
            .HasMajorGridlines = False
            .HasMinorGridlines = False
        End With
        With .PlotArea
            With .Border
                .ColorIndex = 16
                .Weight = xlThin
                .LineStyle = xlNone
            End With
            .Interior.ColorIndex = xlNone
        End With
    End With
    
End Sub

**mineralfellow** · 06-04-2009, 01:32 PM

This is great for putting depth on the vertical, but I want to plot kaolinite vs min grain size. When I put "shtTemp.Columns(5)" into the code, the line .XValues = rngXData" returns an error. I'm not really sure what the .Offset command does. Is that what needs to be modified?

**Andy Pope** · 06-05-2009, 05:50 AM

The offset describes a location in relation to a starting point.

So when the starting point is for example A1:A10 an offset(0,3) would be D1:D10. which is 0 rows and 3 columns to the right.

Currently the code is plotting column A and 1 other column which is user chosen.

You can change it so initial the Y data is set to column A and then change it using the offset.

Code:

' just get the rows to include based on column A
            Set rngMyData = Intersect(shtTemp.UsedRange, shtTemp.Columns(1))
            Set rngMyData = rngMyData.Offset(1, 0).Resize(rngYData.Rows.Count - 1, 1)

' assign X and Y in relation to data in column A
            Set rngXData = rngMyData.Offset(0, lngDataOffset)
            Set rngYData = rngMyData.Offset(0, lngDataOffset)

**mineralfellow** · 06-05-2009, 08:07 PM

Works beautifully. Thank you very much for your patience and help.

Thread: Use VBA to make a chart using a column selected at the start of the macro

Thread Tools

Display

Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Re: Use VBA to make a chart using a column selected at the start of the macro

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions