Solved

How to find if a specific chart series name exists - SeriesCollection?

Posted on 2004-03-22
7
433 Views
Last Modified: 2011-09-20
I want to create some conditional formatting in a chart e.g. the crosstab query on which the chart is based will return different numbers of fields but I want the colours to remain constant for each field.  For instance the chart may return fields fld1, fld2, fld3 on the first query but only fld1 and fld3 on the next occasion.  I want fld3 to always be red, additionally I have to create a legend - the built in legend does not work properly with a two axis chart, it displays the series in the order 1,3,2,4 instead of 1,2,3,4.

How can I assign a colour to a particular series.  Ive tried the following

Dim cht As Object
Dim sr As Series

Set cht = Me!MyChart.Object
   
If cht.SeriesCollection = "fld3" Then sr.Border.ColorIndex = 3

But this produces an error 'Object doesn't support this property or method'.

I've tried using the Object Browser but I haven't found it very helpful because it seems to assume the user knows precisely what he is looking for but in my case I don't! I need to know how to test if a Series named 'fld1' exists and searching under Chart, Series, SeriesCollection or Name doesn't give me a clear enough answer.  Guidance on the use of the Object Browser also appreciated.

Thanks

Mike Collard



0
Comment
Question by:Snowsride
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 10650421
Greetings Snowsride!

  Are you looking for the
   cht.SeriesCollection.name = "fld3"

  or are you trying to specify One Series from collection
   cht.SeriesCollection.Series("name_or_number") = "fld3"

regards
jack
0
 

Author Comment

by:Snowsride
ID: 10650914
Hi Jack

I'm looking for the cht.SeriesCollection.name="fld3" - I've tried this code as well and get the same error.

Mike Collard
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10650996
Seriescollection does not have a name property itself, it's name is SeriesCollection.
You are trying to test the name of a single series in the collection.  

Stop the code at the point you are trying to reference this property.

In the Immediate (Debug) window try various permutations of the object.tree to see if you can get values back from the engine on this object structure.

?cht.name
?cht.SeriesCollection.count
?cht.SereisCollection(0).name

etc

This will give you a visual working method of "seeing what's under the hood" until you can get to the exact naming syntax you want.

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Snowsride
ID: 10652735
I must be on the wrong track entirely here - none of the suggestions work.

All I want to be able to do is format the data series in a chart depending on the name of the data series.  If a series represents the data in fld1 then format it red, if series represents the data in fld2 then format it blue.

So I need a method to return the series name so that it can be compared with the field names in my list.

It seems such a simple thing...!

Mike Collard
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10652797
The series name or the ordinal number of the series you are trying to modify a property of.

What errors are being reported as you try to tap the object structure properties for values?

Sometimes that which seems simple to us is because we are human and can rationalize nearly everything...
0
 

Author Comment

by:Snowsride
ID: 10653006
Thanks - have given up - too difficult.
0
 
LVL 32

Accepted Solution

by:
jadedata earned 250 total points
ID: 10669130
Can we close the question up?
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question