Drawing Layer Objects "Dropping Out"

  • Thread starter Thread starter kat
  • Start date Start date
K

kat

Sorry if this is a common question, I scanned through 18
pages of this newsgroup and didn't see anything that
related to my question. Anyway, on to the question...

I have a chart (obviously) that plots information for a
team meeting. The presentation is fairly complex, the
data is plotted on both axis, and there are several chart
types supported on the chart. My problem is that we also
do an automated assessment of the information and want the
color coded "dashboard" to appear on the graph in the
drawing layer. The dashboard is comprised of several named
text objects with colored backgrounds. I have a VBA
module that grabs the info from the spreadsheet, does the
assessment, changes the colors for the drawing objects.
This code runs on worksheet select. I made certain when
creating the dashboard that the drawing objects were
attached to the chart. However, when viewing the chart,
often times parts or even the entire dashboard "drop out"
and cannot be viewed unless you select the chart object.
Also, when saving the chart to jpg the dropout occurs at
times as well, leaving the chart useless for presentation
purposes.

Anyone got any hints, tips, suggestions? Am I dealing
with a bug here or did I do something wrong in the
creation? Is it possible that I'm just stressing the
memory on the computer with the chart display?

Thanks for your assistance.
kat
 
Hi Kat -

First, you could try searching the group archives, at
http://www.google.com/advanced_group_search, which would likely be more
helpful than scanning pages of subject lines in the web interface.
Second, you could use the news server directly for a nicer newsgroup
experience: point a newsreader (Outlook Express or Netscape can do this)
at the news server msnews.microsoft.com. Sorry for the diversion.

And third, on to your problem. If the chart is selected when you draw
the objects, they will become part of the chart. If the chart is not
selected, the objects may appear in front of the chart (or behind, if
the chart is drawn later), and when you select (or export) the chart,
they disappear. I'm not sure how you said you attached the objects to
the chart, but if you don't reference them in VBA through the chart, in
a syntax like this, they are not part of the chart:

activesheet.chartobjects(1).chart.shapes(1).select
activechart.shapes(1).select

- Jon
 
Thanks for the pointers, Jon, I did try several google
searches prior to posting the newsgroup request, (even
found your website), will have to dig deeper next time.

On to your response/question. The drawing objects are
definately part of the chart (referring to them via
activecharts.shape.select method). Any further
suggestions/have you seen this problem before?

kat
 
Kat -

It's inefficient, but since you said selecting the chart object made the
drawing objects show up, it might be worthwhile to select the chart
object in code:

set o = activechart : activesheet.range("A1").select : o.parent.select

It seems as though the screen isn't being redrawn. Did you set
screenupdating to false or calculation to manual?

- Jon
 
Calclations are automatic, screens are being redrawn.
While selecting the chart with code works, the clients
often need to see the data that it refers to on the same
page.

I've tried grouping the object, to no avail, am thinking
maybe I need to remove the objects and recreate them at
each worksheet select, but that seems inefficient, and in
the meeting environment have multiple workbooks open (18),
and really wanted to keep the processing down to a
minumum. The spreadsheet application really belongs in a
database, but due to client restrictions on database
development, security requirements, and network
limitations, I'm stuck in Excel for this one.

I appreciate your persistance in trying to help me on this
one.

kat
 
Back
Top