Trouble with Excel Named Range and Charts

[socomfort]

Hello,

I am exporting data from a MS Access database to an existing spreadsheet. This data is used as the SourceData of a chart.

I create a named range on the 'Open Workbook' event as follows:

   Private Sub Workbook_Open()
       ThisWorkbook.Names.Add Name:="dynamRange1", _
       RefersTo:= "=OFFSET(ExportData!$A$2,0,0, COUNTA _
       (ExportData!$D:$D)-1,4)", Visible:=True
   End Sub

I would like to use this dynamic range in my chart so that it will refresh every time (without being changed manually). I tried typing the name into the Data Range field in the chart and it only changes it temporarily.

Does anyone know how to make named range reference for the chart SourceData stick? I saw an older thread and in it SkipVought had referenced his post in VB5 & 6 forum, but I could not find it.

thanks,

Ben

 

[PHV]

Have a look here:

http://office.microsoft.com/en-us/excel/HA011098011033.aspx

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=518

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

I have ont been able to make a named range "stick" in that tab.

Rather, I use named ranges in the Series Tab - One for each series.

Skip,

_[red][/red]

 

[socomfort]

Hi PHV,

Thanks for the links. I will peruse them at length.

Hi Skip,

So instead of dynamically creating named ranges for the SourceData, you create them for each series. Is it at that point that you type in the Name and does that stay resident in the tab, or does it require code?

I am guessing that the links PHV provided point me in the right direction also. Thanks to both of you for your kind help.

Ben

 

[SkipVought]

No code. I use the range name for each series' data in each value textbox in the form...
[tt]
=SheetName!SeriesRangeName
[/tt]


Skip,

_[red][/red]