Prev: Saving File with names from Text Box's
Next: Procedure to list subs and fuctions in a custom xla
From: Paul E on 27 Dec 2009 13:29
I have a sheet in a Workbook that holds about 21 charts. When I add data to
the workbook, I need to go into every chart and add rows to each data series
as well as the Horizontal Axis. Since this is tedious, I wrote a Macro to do
this. The Macro works great...but when I run it, any chart that is off-screen
disappears. It still exists, but the only way I can make it visible again is
to save the workbook, close it, and reopen it. I have tried to refresh the
graphs as I update them, but that hasn't fixed it.

The only fix that I have found that works is to temporarily set the Window
zoom to 10% so all the graphs are visible, update the chart ranges, then
reset the Window to the value it was when the Macro ran.

Any idea why the off-screen charts are disappearing? Any suggestions on
eliminating this issue? I'd rather run the Macro w/ ScreenUpdating off so it
will run a bit faster w/out the user seeing the zoom resets, but that defeats
the purpose.

If necessary, I can post the code.

Thanks,
Paul
From: Barb Reinhardt on 27 Dec 2009 14:27
Instead of writing a macro, I've used dynamic named ranges for the chart
series.

Read here about how to set up dynamic charts.

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

If you want help with the code, you probably ought to post it.
--
HTH,

Barb Reinhardt



"Paul E" wrote:

> I have a sheet in a Workbook that holds about 21 charts. When I add data to
> the workbook, I need to go into every chart and add rows to each data series
> as well as the Horizontal Axis. Since this is tedious, I wrote a Macro to do
> this. The Macro works great...but when I run it, any chart that is off-screen
> disappears. It still exists, but the only way I can make it visible again is
> to save the workbook, close it, and reopen it. I have tried to refresh the
> graphs as I update them, but that hasn't fixed it.
>
> The only fix that I have found that works is to temporarily set the Window
> zoom to 10% so all the graphs are visible, update the chart ranges, then
> reset the Window to the value it was when the Macro ran.
>
> Any idea why the off-screen charts are disappearing? Any suggestions on
> eliminating this issue? I'd rather run the Macro w/ ScreenUpdating off so it
> will run a bit faster w/out the user seeing the zoom resets, but that defeats
> the purpose.
>
> If necessary, I can post the code.
>
> Thanks,
> Paul
From: Paul E on 27 Dec 2009 16:20
That's a great best practice and one I use for all my charts. Here's the
problem. The workbook I am using is used by hundreds of CEOs internationally.
It is made available through a CEO peer advisory group. The sheet is set up
w/ hard-coded starting points, and as users update the data, they must either
do what I'm doing or implement dynamic range names. I am going to make this
macro (VB code) available to all users. So, I need to code it to the standard
and lowest common denominator. So...in this case, I really need to solve the
problem since the graph construction an implementation of Dynamic named
ranges is outside my control.

But thanks for the suggestion!

"Barb Reinhardt" wrote:

> Instead of writing a macro, I've used dynamic named ranges for the chart
> series.
>
> Read here about how to set up dynamic charts.
>
> http://peltiertech.com/Excel/Charts/DynamicChartLinks.html
>
> If you want help with the code, you probably ought to post it.
> --
> HTH,
>
> Barb Reinhardt
>
>
>
> "Paul E" wrote:
>
> > I have a sheet in a Workbook that holds about 21 charts. When I add data to
> > the workbook, I need to go into every chart and add rows to each data series
> > as well as the Horizontal Axis. Since this is tedious, I wrote a Macro to do
> > this. The Macro works great...but when I run it, any chart that is off-screen
> > disappears. It still exists, but the only way I can make it visible again is
> > to save the workbook, close it, and reopen it. I have tried to refresh the
> > graphs as I update them, but that hasn't fixed it.
> >
> > The only fix that I have found that works is to temporarily set the Window
> > zoom to 10% so all the graphs are visible, update the chart ranges, then
> > reset the Window to the value it was when the Macro ran.
> >
> > Any idea why the off-screen charts are disappearing? Any suggestions on
> > eliminating this issue? I'd rather run the Macro w/ ScreenUpdating off so it
> > will run a bit faster w/out the user seeing the zoom resets, but that defeats
> > the purpose.
> >
> > If necessary, I can post the code.
> >
> > Thanks,
> > Paul
From: Paul E on 27 Dec 2009 16:26
PS...another problem that we would have if I could get the hundreds of users
worldwide to use Dynamic named ranges is that the columns that are being used
in graphs are columns w/ formulas. So, when the sheet is set up, the formulas
are pasted forward for years so the end-users simply have to add raw data to
the column to the left of the running totals column. So you don't have empty
cells beyond the ones that are being graphed.

"Barb Reinhardt" wrote:

> Instead of writing a macro, I've used dynamic named ranges for the chart
> series.
>
> Read here about how to set up dynamic charts.
>
> http://peltiertech.com/Excel/Charts/DynamicChartLinks.html
>
> If you want help with the code, you probably ought to post it.
> --
> HTH,
>
> Barb Reinhardt
>
>
>
> "Paul E" wrote:
>
> > I have a sheet in a Workbook that holds about 21 charts. When I add data to
> > the workbook, I need to go into every chart and add rows to each data series
> > as well as the Horizontal Axis. Since this is tedious, I wrote a Macro to do
> > this. The Macro works great...but when I run it, any chart that is off-screen
> > disappears. It still exists, but the only way I can make it visible again is
> > to save the workbook, close it, and reopen it. I have tried to refresh the
> > graphs as I update them, but that hasn't fixed it.
> >
> > The only fix that I have found that works is to temporarily set the Window
> > zoom to 10% so all the graphs are visible, update the chart ranges, then
> > reset the Window to the value it was when the Macro ran.
> >
> > Any idea why the off-screen charts are disappearing? Any suggestions on
> > eliminating this issue? I'd rather run the Macro w/ ScreenUpdating off so it
> > will run a bit faster w/out the user seeing the zoom resets, but that defeats
> > the purpose.
> >
> > If necessary, I can post the code.
> >
> > Thanks,
> > Paul
From: Paul E on 27 Dec 2009 16:51
OK...I still have my original issue, but with respects to the note I posted
in regards to the formulas in the columns to be mapped...if I had control
over everyone's spreadsheets, I would set a conditional in the formula that
would hold the cell = "" if the raw data cell was empty. But, again, this is
something over which I have no control...so still have to solve the "case of
the disappearing charts!" :-)

"Paul E" wrote:

> PS...another problem that we would have if I could get the hundreds of users
> worldwide to use Dynamic named ranges is that the columns that are being used
> in graphs are columns w/ formulas. So, when the sheet is set up, the formulas
> are pasted forward for years so the end-users simply have to add raw data to
> the column to the left of the running totals column. So you don't have empty
> cells beyond the ones that are being graphed.
>
> "Barb Reinhardt" wrote:
>
> > Instead of writing a macro, I've used dynamic named ranges for the chart
> > series.
> >
> > Read here about how to set up dynamic charts.
> >
> > http://peltiertech.com/Excel/Charts/DynamicChartLinks.html
> >
> > If you want help with the code, you probably ought to post it.
> > --
> > HTH,
> >
> > Barb Reinhardt
> >
> >
> >
> > "Paul E" wrote:
> >
> > > I have a sheet in a Workbook that holds about 21 charts. When I add data to
> > > the workbook, I need to go into every chart and add rows to each data series
> > > as well as the Horizontal Axis. Since this is tedious, I wrote a Macro to do
> > > this. The Macro works great...but when I run it, any chart that is off-screen
> > > disappears. It still exists, but the only way I can make it visible again is
> > > to save the workbook, close it, and reopen it. I have tried to refresh the
> > > graphs as I update them, but that hasn't fixed it.
> > >
> > > The only fix that I have found that works is to temporarily set the Window
> > > zoom to 10% so all the graphs are visible, update the chart ranges, then
> > > reset the Window to the value it was when the Macro ran.
> > >
> > > Any idea why the off-screen charts are disappearing? Any suggestions on
> > > eliminating this issue? I'd rather run the Macro w/ ScreenUpdating off so it
> > > will run a bit faster w/out the user seeing the zoom resets, but that defeats
> > > the purpose.
> > >
> > > If necessary, I can post the code.
> > >
> > > Thanks,
> > > Paul
 | 
Pages: 1
Prev: Saving File with names from Text Box's
Next: Procedure to list subs and fuctions in a custom xla