?
Solved

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

Posted on 2004-03-22
7
Medium Priority
?
445 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 500 total points
ID: 10669130
Can we close the question up?
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

762 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