Make Chart refer to user defined worksheet

[atja]

Hi,

I have a question about selecting chart data in Excel.

My workbook has a number of worksheets, which have exactly the same
layout, but different data on them. On another worksheet I have a
chart that plots data from one of these worksheets. Now I want to make
an option such that a user can enter the name of a worksheet in a cell
on the worksheet with the plot, so the plot shows the data from that
specific worksheet.

Any idea how I can do this? Any help would be highly appreciated!

Thanks,
atja

 

[Don Guillett]

For something like this I would use a defined name for my series and
then a macro to RE-define the sheet part of the named series from a
drop down. If all else fails, send me your file at dguillett1
@gmail.com

 

[isabelle]

hi atja,


Dim sht As Worksheet, x As String
x = ActiveSheet.Range("A1") ' adapt x
Set sht = Sheets(x)

With ActiveSheet.ChartObjects(1).Chart
.SetSourceData Source:=sht.Range("A1:C3"), PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name
End With



--
isabelle



Le 2012-01-26 08:45, atja a écrit :

 

[atja]

Thanks for your quick answers!
Don Guillett, I tried to make a dropdown, but have a problem. I'm used
to the 'old' Excel, but I'm now using version 2010. And the whole
Visual Basic seems to have changed. Now I don't even get a combobox/
dropdown to fill with the names of my worksheets. Stupid question, but
could you maybe help me with this?

 

[Don Guillett]

Sure, send me your file to dguillett1 @gmail.com along with this msg
and examples.