Solved

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

Posted on 2004-03-22
7
422 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now