Solved

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

Posted on 2004-03-22
7
436 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

738 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