PDA

View Full Version : Chart data based on user input



leal72
09-16-2009, 10:13 AM
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. :banghead:

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
09-16-2009, 11:51 AM
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



Sub ResetData()

With ActiveSheet.ChartObjects("myChart").Chart

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

Case "btnSheet2"

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

Case "btnSheet3"

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

leal72
09-16-2009, 12:32 PM
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
09-16-2009, 01:14 PM
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
09-16-2009, 01:42 PM
I ran the code as you posted but I get a


Run-time error '1004':

Unable to get the Buttons property of the Worksheet class


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.


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


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
09-17-2009, 01:20 PM
- sorry, double post

leal72
09-17-2009, 01:27 PM
I was able to get it going with the code below

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

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.