Graph data series from several different sheets

[llama24601]

Hi! I'm in the process of creating a macro which creates an xy graph from data chosen using input boxes. I've run into two problems (one major and one minor).
1. I am given an error message whenever I try to select data from a different worksheet. How can I make the macro able to use data from several different worksheets? (! suspect that the problem is under the comment, "Set additional y values with input box", but I'm not sure)
2. No matter what I set as the name for series 1, the legend always says Series 1. How can i make it correspond to myYTitle?
Any suggestions would be greatly appreciated! Thank you!

[B]Sub InProgress()[/B]
With ActiveSheet
‘
‘
‘ Does correct graphing for MANY PAIRS of data columns! Hooray!
‘ Also includes input box for chart title!
‘
‘
‘ Set x values with input box
Dim myXCell As Range
Dim myXSeries As Range
Dim myXTitle As Range
Set myXTitle = Application.InputBox(“Please select the heading of the column which contains your desired X values:”, “Select title cell”, Type:=8)
myXTitle.Offset(1, 0).Select
Set myXCell = Selection
Range(myXCell, myXCell.End(xlDown)).Select
Set myXSeries = Selection
‘
‘
‘ Set y values with input box
Dim myYCell As Range
Dim myYSeries As Range
Dim myYTitle As Range
Set myYTitle = Application.InputBox(“Please select the heading of the column which contains your desired Y values:”, “Select title cell”, Type:=8)
myYTitle.Offset(1, 0).Select
Set myYCell = Selection
Range(myYCell, myYCell.End(xlDown)).Select
Set myYSeries = Selection
‘
‘
‘ Create Blank Graph
Dim chartObj As ChartObject
Dim DataChart As Chart
Set chartObj = ActiveSheet.ChartObjects.Add(Top:=10, Left:=325, Width:=600, Height:=300)
Set DataChart = chartObj.Chart
DataChart.ChartType = xlXYScatterSmooth
‘
‘
‘ Remove auto-plotted data
Do While DataChart.Seriescollection.count > 0
DataChart.Seriescollection(1).Delete
Loop
‘
‘
‘ Add first data series
With DataChart.SeriesCollection.NewSeries
.Name = myYTitle
.XValues = myXSeries
.Values = myYSeries
End With
‘
‘
'
‘ Formatting
DataChart.ChartTitle.Select
DataChart.ChartTitle.Text = “Title”
Selection.Format.TextFrame2.TextRange.Characters.Text = “Title”
With Selection.Format.TextFrame2.TextRange.Characters(1, 5).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 5).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = “Arial”
.NameFarEast = “+mn-ea”
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 16
.Italic = msoFalse
.Kerning = 12
.Name = “Arial”
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
‘
‘
‘
‘
‘ Display a message box with yes/no and question icon – want to continue?
If MsgBox(“Would you like to add another Y data series to your graph?”, vbQuestion + vbYesNo + vbDefaultButton2, “Continue?”) = vbYes Then
MsgBox “The user clicked Yes”
‘
‘
‘
‘ BEGIN THE LOOP of selecting additional Y values until user selects NO
Do Until answer = vbNo
‘
‘ Set additional y values with input box
Dim myAddCell As Range
Dim myAddSeries As Range
Dim myAddTitle As Range
Set myAddTitle = Application.InputBox(“Please select the heading of the column which contains the Y values you want to add:”, “Select title cell”, Type:=8)
myAddTitle.Offset(1, 0).Select
Set myAddCell = Selection
Range(myAddCell, myAddCell.End(xlDown)).Select
Set myAddSeries = Selection
‘
‘
‘ Add the new data to graph
With DataChart.SeriesCollection.NewSeries
.Name = myAddTitle
.Xvalues = myXSeries
.Values = myAddSeries
End With
‘
‘
‘ Display message box with yes/no and question icon
answer = MsgBox(“Would you like to continue and select another Y data series?”, vbQuestion + vbYesNo + vbDefaultButton2, “Continue?”)
‘ END OF LOOP
Loop
Else
MsgBox “The user clicked No”
End If
‘
‘
‘
‘ Add a chart title and axis labels with input box
With DataChart
.HasTitle = True
.ChartTitle.Text = Application.InputBox(“Please enter a chart title”, “Chart Title Name”, Type:=2)
‘ Add X Axis title
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = myXTitle
‘ Add Y Axis title with input box?
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Application.InputBox(“Please enter the Y axis title”, “Y axis Name”, Type:=2)
End With
‘
‘
End With
End Sub

 

[pgc01]

Hi llama
Welcome to the board

You don't usually use Select or Selection in vba. It's inefficient and sometimes will not work directly like in your case.

I copied the first part of your code and changed the part where you select.

Please try.

Sub InProgress()

With ActiveSheet
'
'
' Does correct graphing for MANY PAIRS of data columns! Hooray!
' Also includes input box for chart title!
'
'
' Set x values with input box
Dim myXCell As Range
Dim myXSeries As Range
Dim myXTitle As Range

Set myXTitle = Application.InputBox("Please select the heading of the column which contains your desired X values:", "Select title cell", Type:=8)
Set myXCell = myXTitle.Offset(1, 0)
Set myXSeries = Range(myXCell, myXCell.End(xlDown))
'
'
' Set y values with input box
Dim myYCell As Range
Dim myYSeries As Range
Dim myYTitle As Range

Set myYTitle = Application.InputBox("Please select the heading of the column which contains your desired Y values:", "Select title cell", Type:=8)
Set myYCell = myYTitle.Offset(1, 0)
Set myYSeries = Range(myYCell, myYCell.End(xlDown))
'
'
' Create Blank Graph
Dim chartObj As ChartObject
Dim DataChart As Chart

Set chartObj = ActiveSheet.ChartObjects.Add(Top:=10, Left:=325, Width:=600, Height:=300)
Set DataChart = chartObj.Chart
DataChart.ChartType = xlXYScatterSmooth
'
'
' Remove auto-plotted data
Do While DataChart.SeriesCollection.Count > 0
DataChart.SeriesCollection(1).Delete
Loop
'
'
' Add first data series
With DataChart.SeriesCollection.NewSeries
    .Name = myYTitle
    .XValues = myXSeries
    .Values = myYSeries
End With

End With ' with ActiveSheet
End Sub

 

[llama24601]

Hi llama
Welcome to the board

You don't usually use Select or Selection in vba. It's inefficient and sometimes will not work directly like in your case.

I copied the first part of your code and changed the part where you select.

Please try.

Sub InProgress()

With ActiveSheet
'
'
' Does correct graphing for MANY PAIRS of data columns! Hooray!
' Also includes input box for chart title!
'
'
' Set x values with input box
Dim myXCell As Range
Dim myXSeries As Range
Dim myXTitle As Range

Set myXTitle = Application.InputBox("Please select the heading of the column which contains your desired X values:", "Select title cell", Type:=8)
Set myXCell = myXTitle.Offset(1, 0)
Set myXSeries = Range(myXCell, myXCell.End(xlDown))
'
'
' Set y values with input box
Dim myYCell As Range
Dim myYSeries As Range
Dim myYTitle As Range

Set myYTitle = Application.InputBox("Please select the heading of the column which contains your desired Y values:", "Select title cell", Type:=8)
Set myYCell = myYTitle.Offset(1, 0)
Set myYSeries = Range(myYCell, myYCell.End(xlDown))
'
'
' Create Blank Graph
Dim chartObj As ChartObject
Dim DataChart As Chart

Set chartObj = ActiveSheet.ChartObjects.Add(Top:=10, Left:=325, Width:=600, Height:=300)
Set DataChart = chartObj.Chart
DataChart.ChartType = xlXYScatterSmooth
'
'
' Remove auto-plotted data
Do While DataChart.SeriesCollection.Count > 0
DataChart.SeriesCollection(1).Delete
Loop
'
'
' Add first data series
With DataChart.SeriesCollection.NewSeries
    .Name = myYTitle
    .XValues = myXSeries
    .Values = myYSeries
End With

End With ' with ActiveSheet
End Sub

Thank you so much! The new code worked perfectly!

 

[llama24601]

The graphing worked PERFECTLY (Although the series 1 name in the legend still refuses to change, but that's a much more minor detail!)