colorIndex listing for Chart with autoformat settings

Hi,

Is there an access through VBA  to the listing of the Border.ColorIndex
sequence used by Excel  for      SeriesCollection(n)       in
dependence of  "n"
in Charts based on default / autoformat settings  ?

All my trials to acceed to this parameter for individual curves lead to
" - 4105"   :-((

Thanks in advance
Regards

Jean

0
jean.ruch (52)
10/13/2005 10:39:39 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
515 Views

Similar Articles

[PageSpeed] 38

-4105 means default, as you must have guessed.

Getting the color of default-formatted series is not straightforward. 
You can start by figuring the color index of the bottom two rows of the 
palette, which Excel uses as default chart colors. They start at 25 (the 
dark blue in the bottom left) and increase by one.

However, depending on the history of the chart, the colors follow 
different sequences. Suppose I have a chart with series A, B, and C. 
These have default colors 25, 26, and 27. Then I delete series B. A and 
C keep the formatting they started with. Then I add series D, which is 
plotted third in the plot order, but it takes on the formatting of the 
departed series B (so they go 25-27-26 in sequence). Further, if I query 
the series using the old fashioned XLM technique:

?ExecuteExcel4Macro("selection()")

Excel tells me that series D is also series 2. So the series default 
format is tied to the series number as interpreted by XLM, which is 
related to the order in which the series are added to the chart, keeping 
in mind that any gaps in the sequence are filled by the next added 
series. This is different than the series number as interpreted by VBA 
(which is closely related to plot order).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Jean Ruch wrote:

> Hi,
> 
> Is there an access through VBA  to the listing of the Border.ColorIndex
> sequence used by Excel  for      SeriesCollection(n)       in
> dependence of  "n"
> in Charts based on default / autoformat settings  ?
> 
> All my trials to acceed to this parameter for individual curves lead to
> " - 4105"   :-((
> 
> Thanks in advance
> Regards
> 
> Jean
> 
0
10/14/2005 1:28:44 AM
"Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> schrieb im
Newsbeitrag news:%238qhe6F0FHA.2640@TK2MSFTNGP10.phx.gbl...
> -4105 means default, as you must have guessed.
>
> Getting the color of default-formatted series is not straightforward.
> You can start by figuring the color index of the bottom two rows of
the
> palette, which Excel uses as default chart colors. They start at 25
(the
> dark blue in the bottom left) and increase by one.
>
> However, depending on the history of the chart, the colors follow
> different sequences. Suppose I have a chart with series A, B, and C.
> These have default colors 25, 26, and 27. Then I delete series B. A
and
> C keep the formatting they started with. Then I add series D, which
is
> plotted third in the plot order, but it takes on the formatting of
the
> departed series B (so they go 25-27-26 in sequence). Further, if I
query
> the series using the old fashioned XLM technique:
>
> ?ExecuteExcel4Macro("selection()")
>
> Excel tells me that series D is also series 2. So the series default
> format is tied to the series number as interpreted by XLM, which is
> related to the order in which the series are added to the chart,
keeping
> in mind that any gaps in the sequence are filled by the next added
> series. This is different than the series number as interpreted by
VBA
> (which is closely related to plot order).
>
> - Jon
> -------

many thanks, Jon

Your input is - as usual - exactly what I needed for my purpose :
My Sub makes a listing - in the sheet -  of the equation of the
trendlines of
complete arrays of curves showed in an embedded Chart .
In order to be able to assign at a glance the equation to it's
respective curve,
my aim was  to color the font of the equation in the color of this
latter (on a greyed background of course....).
This works in my sub as intended only in the case where the color
of the curves has been assigned. (in the worst case manually .....)

As the operation takes place in a closed loop without any possibility
of manual intervention and as long as  the Diagramm should not be
different
of  what is get usually, all will be easy with your information.

Thanks again

Jean

0
jean.ruch (52)
10/14/2005 7:55:04 AM
Reply:

Similar Artilces:

Not in list where cbo is null ie. tab through
Hi knowledgeable gurus, I have made use of "not in list" but in this instance I know that the record doesn't exist & as such I want to just tab through but invoke the "Not in List" sub. Can I do that in a before update without duplicating the whole code? What should I be doing as I don't want to add extra buttons etc You can just call the NotInList code directly with CALL Yourcbo_NotInList but I would lean toward executing a similar but different procedure. You can not pass the Response variable to the procedure so the code will almost certainly be...

How do I chart date ranges with varying start and finish dates?
I want to create a chart that on one axis will show date ranges that have varying start and finish dates. The other axis is a list of project names. Example: Project #1, starts 5/1/05, ends 10/1/05; Project #2 starts 3/5/05, ends 2/2/06, etc. I want a bar chart that I can sort. Any help is very appreciated. : ) It sounds like you need to create a Gantt chart. Here are some resources that might help: http://www.peltiertech.com/Excel/Charts/GanttLinks.html http://www.peltiertech.com/Excel/Charts/GanttChart.html http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343 ----...

How to shift address listings from row list to columns?
I have a mailing list with name, address, city, state & zip with each item in individual rows like a list of labels and a few empty rows of space between each listing. How do I create/transfer this list into columns accross so I can sort by city or zip? Thank you! If your data is nicely grouped, with each group in 5 lines, viz: name add city state zip then an earlier suggestion given which worked might be worth a try: See: http://tinyurl.com/wgcb -- hth Max ----------------------------------------- Please reply in newsgroup Use xdemechanik <at>yahoo<dot>com for email --...

setting up multiple email accounts #2
I followed the rules for setting up the pop3 and smtp accounts for multiple mail accounts, but when I access outlook express the additiona account does not recognize the password I've asked it to remember. HELP! KCB <anonymous@discussions.microsoft.com> wrote: > I followed the rules for setting up the pop3 and smtp > accounts for multiple mail accounts, but when I access > outlook express the additiona account does not recognize > the password I've asked it to remember. HELP! Ask in an Outlook Express newsgroup. -- Brian Tillman ...

Charts #3
If I want my chart to read from 50 to 100 instead of 0 to 100 How do I do this? -- Richard Northern California Be Nice to Everyone Everyday WHERE? -- Richard Northern California Be Nice to Everyone Everyday "jr" <kiii63@hotmail.com> wrote in message news:00fc01c36813$5688d060$a601280a@phx.gbl... > double click on the current chart axis values on the > desired chart axis. You can set the scale in the popup > window. > >-----Original Message----- > >If I want my chart to read from 50 to 100 instead of 0 > to 100 How do I do > >this? > >...

avoiding duplicate in list
Dear All, I have created a list with a name for ex A1 : A25 named as number in this number list there are few numbers repeated i want to list the date without duplicating. I want to list the above date in a cell (using DATA >> VALIDATION >> LIST Cud plz help on this... Thanx and regds. CNP -- chinaprasad ------------------------------------------------------------------------ chinaprasad's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8621 View this thread: http://www.excelforum.com/showthread.php?threadid=267171 Hi see: http://www.cpearson....

High score list?
How do I create a high score list for an everlasting soccer tournament? Perhaps something along these lines .. (Only takes 5 mins to set-up <g>) Assume the table below is in Sheet1, cols A to C, data from row2 down Game# Scorer Goals 00001 ABC 1 00002 XYZ 3 00003 ABC 3 00004 XYZ 2 Put in E2: =IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",ROW())) Copy E2 down to say, E1000 to cover the max expected number of rows of data in the table Col E will flag and assign uniques in col B with an arbitrary row number (for us to extract the list of unique scorers in Sh...

How to slant cells in Excel on top of chart at about 45 degrees
Trying to make a chart and slant cells at the top at about a 45 degree angle with borders and still be able to type into it. Is this possible? ckricci Wrote: > Trying to make a chart and slant cells at the top at about a 45 degree > angle > with borders and still be able to type into it. Is this possible? HI ckricci Try Format > Cells > Alignment Tab and set the Orientation to 45 degress -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2...

Can Line Chart Overlaps be Highlighted?
Can the overlap portion(s) of two lines in a chart be shown in a different color, without having to manually draw a shape? I would like to show the areas where Line A is above Line B as red, and the areas where it crosses below in blue - is it possible to do this without manually drawing in shapes? Hi, To some extent it depends how many times the lines cross. If it's just the once then you can use area charts to provide the shading. http://www.andypope.info/ngs/ng21.htm http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=590 This may help if the used one line as a base line...

validation list or combo box dependant on cell value
Am i able to determine the values shown in either a validation list or combo box being dependant on a value in another cell? ie: Cell A1 = BOB then validation or combo box would then base it's list from the named range (or whatever the solution may be) based on Bob. if i was to change A1 to ROY then it would also change the underlying list? I have tried everything that i think SHOULD work but that it pretty limited... thanks in advance rich I'd start with Debra Dalgleish's site: http://contextures.com/xlDataVal02.html Richard Edwards wrote: > > Am i able to deter...

Contact list not recognized as an address book in outlook email
I installed Outlook 2007 on my computer and imported my addresses from my other computer's Outlook file with excel. The contact list shows all the addresses but I can not access the contact list when I try to send an email. The address book drop down menu does not show anything. I tried to do the "properties" on the contact file, selected the tab "Outlook Address Book" and I am not allowed to check the box to "Show this folder as an address book". I am hoping someone can help me get this resolved. Thank you in advance If you have Contacts in...

Help me create sales chart based on state and quantity
We have a production report on excel. It shows the details for our clients. Part of that data includes the state in which the client lives. We are trying to create a chart showing the percentages of each state( so we know where the most deals are closed) Any suggestions? Hello mr_merchant_man, this sounds like a job for a pivot table, using Average as the data calculation operator. Or, depending on your version of Excel, you can use AVERAGEIFS (in Excel 2007) or calculate an averate with a combination of SUMIF divided by COUNTIF. To be more specific, it would help to s...

Outlook Security Settings #2
I am trying to use Outlook security settings. I have installed the settings precisely as described on the MS site and the settings work perfectly for me and one or two others but not for the rest of my defined group. We are using Exchange 2003, Outlook 2003 and Outlook XP. I cannot find any debugging or logging info anywhere and am a little stuck as to what to do next. Can anyone offer any ideas??? jON ...

time line in chart
Hi How do I put a vertical line in a chart in excel at a given point? i.e. timeline Thanks Jon I know this is too elementary, but go from here: You have a scatter chart from A1:B6 (X A1:A6; Y B1:B6) 10 3 11 4 12 5 13 6 14 7 15 8 A 2nd source series is A8:B9 (X A8:A9; Y B8:B9) 13 0 13 6 giving you the a vertical line at X=13. "jon" <jon@nospam.com> wrote: >Hi >How do I put a vertical line in a chart in excel at a given point? i.e. >timeline >Thanks >Jon ----== Posted via Pronews.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.prone...

Help on adding values to a bar chart
Hello, Here is my problem. I currently have a bar chart that has 8 X-axis bars. I need to add 2 more to the X-axis. The labels for the x-axis are based on cells within another spreadsheet, but are not consecutive cells, they are spread apart. So when I go to add the two more cells, I can only add one. When I click on the second cell to add it to the list, all of the previous cells get unselected. I have even tried to manually add these two values in. Is there a limit to how many values can be on the X-axis? Please Help!!! Thanks. To select non-consecutive cells: select first cell, h...

help with Excel Chart, Series name
Hello, I am using ORACLE OLE2 package to print a series bar graph in excel. I need to rename "Series Name" that comes out in as a char in excel. By defual the series are names as Series1, Series2, Series3,.....SeriesN. How can I access the handle to "Series Name" which help me change its name. Any help will be highly appreciated. Thanks you, Bilal Bilal, Manally, you do this with Chart - Source data - Series tab. There's a series name box. Select the series to be edited in the drop-down at the left first. It may have a cell reference currently in it, or you can...

Drop Down List #4
I am trying to insert a drop down list in one worksheet. I have a list of names on worksheet 2 that I would like to drop down. The directions from Help are hard to follow and there seem to be missing steps. HELP See Debra Dalgleish's website for good instructions: http://www.contextures.com/xlDataVal01.html Particularly, the "Name the List Range" section Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Kerry Anne" wrote: > I am trying to insert a drop down list in one worksheet. I have a list of > names on worksheet 2 that I would like to dr...

I am writing a screenplay and am wondering about setting margins easier.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel The way things are now, every time I switch from the narrative to dialogue I have to reset the margins or tabs. I am guessing there is some way to do it in one click but I am pretty weak when it comes to knowing computers. <br><br>Is there a way and if so, please explain it to me in the simplest of terms. <br><br>Thanks! In Word, we typically set the margins once only, for the entire document. We then use Styles to contain the settings for indents and tabs. A style is simply a named...

Paging Size set up for sql 2005 best performance
erver windows 2003 sp1. 64 bits, Paging file size for selected drive C is (custom selected) Initial 2046 MB, and maximum 4092 MB. It is currently allocated 4081 MB. Computer RAM memory is 8GB. Server functions as a database MS SQL 2005 server, and it is used under heavy insert database operations The problem is that it can be seen on windows task manager there is on PF usage and Page File Usage hHstory around 8.2 GB paging file size not 4081 GB as it is given on virtual memory page set up. DoleBludger (DoleBludger@discussions.microsoft.com) writes: > erver windows 200...

outlook contact list
When i try and access my contact list, it says there are no items to be displayed. Same with the public folder employee contact list that everyone has access to. ...

Copy Data from Chart to Chart #2
I have to create separate charts for ten employees. I want to have all the data on one sheet tab. Create the chart on another tab for employee 1, format the chart the way I want and be able to add the data for employee 2, 3, 4, etc. to each tab, using the same format for chart 1 so they all match. So there should be one tab for raw data and 10 tabs for each employee. I’m using Excel 2000 Thank you Karen - Make a chart for the first employee, and make it look just the way you want it. Then right click on the sheet tab, select Move or Copy, and make a copy. Then make eight more copies....

Creating a chart via vba
Can anyone help with some example of how to create a chart via VBA? My problem is that the number of rows varies. Regards /Snedker There may not be any need for VBA. See the Excel | Tutorials | 'Dynamic Charts' page of my web site for options. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <r7ussv44bp0hhjh8tfqircph9nak5kp0hq@4ax.com>, morten@nospam_dbconsult.dk says... > Can anyone help with some example of how to create a chart via VBA? My > problem is ...

printing group of charts
I have workbook with 11 charts, each on separate chart sheet and I want to print them to PDF. I can not have them printed to one file. Instead they are splitted in 6 files, some with single page, other with 6 pages. I can not find out what is causing it. Can I somehow print to PDF and have one file created ? Thanks for help. Tony I have solved my problem. I had to push the page setup for all charts to be the same and now all is working well. I had to change the page sizes for all to A3 and than back to A4 to push excell to see that they are all the same. Tony "Tony" wrote:...

Incorrect Account Appearing on General Transaction Edit List
If I go into my General Ledger and view a certain entry, on the input screen, it lists the correct accounts. However, if I print the edit list, one of the accounts is incorrect. In fact, it is an account that doesn't exist in our system. As shown in transaction entry screen: 51-6021-3500 Disability Insurance $478.24 12-1296-0000 Intercompany Rec/Pay - $478.24 As printed on edit list: 51-6021-1000 Disability Insurance $478.24...

Share Task List
User has Outlook 2003 & we have a corporate Exchange server I have a user that has created a second task folder (File > Folder > New Folder...) They went into Sharing and on the Permissions tab setup the people who should have access. The other people to whom access was given can't seem to find this task folder. In the "Open Other User's Folder" they only have the default Folder type. When they pick tasks it tells them it can't find it. To test this out I had the user give same permissions to the default task and that can be opened no problem. It seems t...