Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 70
Default updating charts

Let me elaborate on Chip's response. The chart series formula contains
addresses of the ranges of data in the chart, but the Values and XValues
are not themselves ranges, they are the arrays of values in these
ranges. If you want to manipulate the ranges you will need to parse the
series formula, figure out what's between which commas, and therefore
which has the data you want.

John Walkenbach has a class module that does this for you much more easily:

http://www.j-walk.com/ss/excel/tips/tip83.htm

If all you want to do is refer to the same range of cells on a different
sheet, you could use the Change Series Formula macros on my web site:

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

You can simply change the text from 'Sheet1' to 'Sheet2', or perhaps a
column from $A to $B, to update a chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Maynard wrote:
I'm attempting to set up a function that will update a chart based on
allowing the user to select which sheet the data is on. There are several
series on each chart, so I'm trying to avoid using named ranges. I can't
seem to figure out why this isn't working. I get "object variable or with
block variable not set" error on the specified line. Thanks for any help!
PS - strShtName is entered on a UserForm.

Dim Rng As Range
Dim Cht As Chart
Dim ChtSeries As Series

For Each Cht In ThisWorkbook.Charts
For Each ChtSeries In Cht.SeriesCollection
Rng = ChtSeries.Values 'ERROR ON THIS LINE!
Rng.Parent.Name = strShtName
Rng = ChtSeries.XValues
Rng.Parent.Name = strShtName
Set Rng = Nothing
Next ChtSeries
Next Cht


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Charts not updating vitorjose Excel Worksheet Functions 1 July 3rd 08 02:36 PM
Updating Charts Mike Charts and Charting in Excel 1 May 9th 07 01:36 PM
updating pie charts Lilsis7 Excel Discussion (Misc queries) 2 April 24th 06 01:21 PM
Automatic Updating Charts Rich Gibbons Charts and Charting in Excel 2 October 9th 05 11:36 PM
updating charts Chip Pearson Excel Programming 0 August 20th 04 06:24 PM


All times are GMT +1. The time now is 06:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"