Chart data based on user input

[leal72]

Having a tough go at how to go about this.

I want to allow the user to select which worksheet to get the series data from.

Right now the workbook has 6 sheets but the number of sheets is never a constant. One sheet contains a chart with data from the other worksheets. The user will look at this chart with multiple series and decide which series (worksheet) that they want to view in it's own chart.

Hope I'm making sense

[Bob Phillips]

Have a selector on the chart sheet that identifies all ofe sheets, and when one is selected build the chart from that one.

Something like

[vba]

Sub ResetData()

With ActiveSheet.ChartObjects("myChart").Chart

Select Case ActiveSheet.Buttons(Application.Caller).Name

Case "btnSheet2"

.SetSourceData Source:=Sheets("Sheet2").Range("C510"), PlotBy:=xlColumns

Case "btnSheet3"

.SetSourceData Source:=Sheets("Sheet3").Range("H10:I15"), PlotBy:=xlColumns
End Select
End With
End Sub
[/vba]

[leal72]

Thank you very much. I'm working on trying this out. Will let you know how it goes. From what I recall about select case, I need to indentify each worksheet as a case?

[Bob Phillips]

That is right, but if the buttons or whatever is based upon the sheet name, you could avoid the Select Case and build from the button name, as long as the data is in the same range (or you use local named ranges) on each sheet

[leal72]

I ran the code as you posted but I get a

[vba]
Run-time error '1004':

Unable to get the Buttons property of the Worksheet class
[/vba]

I have also just switched to Excel 2007 and still getting through the kinks F1 is not doing what it use to for me.

The code I'm working on is pretty long so I didn't want to post it all.

The code overall is opening a data file (CSV) and it adds that file into the workbook, then grabs data from the sheet and plots it on 2 charts. Here is part of it.

[vba]
Set Load = Range("E" & DataStart)
Range(Load, Load.End(xlDown)).Name = "Load"
Set Disp = Range("F" & DataStart)
Range(Disp, Disp.End(xlDown)).Name = "Disp"
Set Stress = Range("G" & DataStart)
Range(Stress, Stress.End(xlDown)).Name = "Stress"
Set Strain = Range("H" & DataStart)
Range(Strain, Strain.End(xlDown)).Name = "Strain"

End With

' plot chart data for each test specimen
Sheets("SummaryData").Select

ActiveSheet.ChartObjects("Chart 1").Activate ' Load vs Cross head displacement
With ActiveChart.SeriesCollection.NewSeries
.Name = WsName
.Values = Sheets(WsName).Range("Load")
.XValues = Sheets(WsName).Range("Disp")
End With

ActiveSheet.ChartObjects("Chart 2").Activate ' Stress, psi vs Strain, in/in
With ActiveChart.SeriesCollection.NewSeries
.Name = WsName
.Values = Sheets(WsName).Range("Stress")
.XValues = Sheets(WsName).Range("Strain")
End With

next i
[/vba]

this is a portion of the loop that plots the data to the charts. From those two charts the user will look at the curves and decide which one they like best and this is where I want the user to select the desired curve and plot it in the 3rd chart.

[leal72]

- sorry, double post

[leal72]

I was able to get it going with the code below

[vba]Curve = InputBox("Select Curve to plot", "Which curve")

Sheets("SummaryData").Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SetSourceData Source:=Sheets(Curve).Columns("G:H")
ActiveChart.SeriesCollection(1).Values = Sheets(Curve).Range("G3:G5000")
ActiveChart.SeriesCollection(1).XValues = Sheets(Curve).Range("H3:H5000")
ActiveChart.SeriesCollection(1).Name = Curve[/vba]

I would prefer to use a List box, Combo box or other similar selector so the user does not have to enter in the sheet name but rather just select it.

and I also want to create code that will allow the user to select which ranges to use. Right now the 5000 is a massive overshoot to make sure I get all the points. I may try select case for the various range possiblities and see how that goes.