Dynamically changing Border-Color of all series() to "0"

Hi,

I had to create a script that transfers data out of ascii-files int
Excel-Sheets. After transfering I wanted to create a Chart  with tha
data using VBA. So far it worked.

Now I'm stuck. I tried to change the color of all the lines to black.

Code
-------------------
    
  For i = 2 To NewChart.SeriesCollection.count 'First DataSeries is "2"
  NewChart.SeriesCollection(c).Select
  With Selection.Border
  .ColorIndex = 1
  .Weight = xlMedium
  .LineStyle = xlContinuous
  End With
  Next 
-------------------


It works, but I have 190 Series in average so it takes about 5 minute
to change the color. I could draw them with permanent marker on m
screen faster. ;) 

So, is it possible to speed that up?

Thank you,

Simo

--
moondar
-----------------------------------------------------------------------
moondark's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2739
View this thread: http://www.excelforum.com/showthread.php?threadid=46911

0
9/20/2005 10:07:22 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
437 Views

Similar Articles

[PageSpeed] 37

Simon -

There are lots of tricks to speed up code. One is to put

Application.ScreenUpdating = False

before the code and

Application.ScreenUpdating = True

after it, especially since Excel handcuffs itself by redrawing the 
entire chart and all of its data points when it changes a single one. 
And I bet 190 series takes a while. What will have a smaller effect is 
to avoid selecting everything before you change it. That means change 
this pair of lines

    NewChart.SeriesCollection(c).Select
    With Selection.Border

to this

    With NewChart.SeriesCollection(c).Border

Also, this might be redundant, as it looks like a default to me

   .LineStyle = xlContinuous

So all in all, you should use code adapted like so:

   Application.ScreenUpdating = False
   For i = 2 To NewChart.SeriesCollection.count 'First DataSeries is "2"
     With NewChart.SeriesCollection(c).Border
       .ColorIndex = 1
       .Weight = xlMedium
       '.LineStyle = xlContinuous
     End With
   Next i
   Application.ScreenUpdating = True

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

moondark wrote:
> Hi,
> 
> I had to create a script that transfers data out of ascii-files into
> Excel-Sheets. After transfering I wanted to create a Chart  with that
> data using VBA. So far it worked.
> 
> Now I'm stuck. I tried to change the color of all the lines to black.
> 
> Code:
> --------------------
>     
>   For i = 2 To NewChart.SeriesCollection.count 'First DataSeries is "2"
>   NewChart.SeriesCollection(c).Select
>   With Selection.Border
>   .ColorIndex = 1
>   .Weight = xlMedium
>   .LineStyle = xlContinuous
>   End With
>   Next i
> --------------------
> 
> 
> It works, but I have 190 Series in average so it takes about 5 minutes
> to change the color. I could draw them with permanent marker on my
> screen faster. ;) 
> 
> So, is it possible to speed that up?
> 
> Thank you,
> 
> Simon
> 
> 
0
9/20/2005 12:05:43 PM
Thanks alot. That's it.
Time it took before deactivating ScreenUpdate: 5min 47sec
Time it took after deactivating ScreenUpdate: *tada* 21sec
Now -that- is fast.

Thx,
Simo

--
moondar
-----------------------------------------------------------------------
moondark's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2739
View this thread: http://www.excelforum.com/showthread.php?threadid=46911

0
9/20/2005 1:40:57 PM
Reply:

Similar Artilces:

CRM 3.0 on Outlook 2007
Hello, I installed the spanish version of CRM Client on my Outlook 2007. I noticed the user interface has changed. With this version any time I click on a CRM entity it pops up an Internet Explorer window. Is this normal or should I still be seing the data On oultook application (that was the case of CRM for Outlook 2003)? Regards, Irene Nothing changed (assuming you are reffering to the v3c update) in this regard. The CRM for Outlook client has typically opened browser pages. The only exception was if you double clicked on the core Outlook items. Basically, if you click from an O...

User status in Sharepoint 3.0
I see there is a user status that works with Office Communicator. Is there away to do this with out office Communicator or free software to do this? We are a small company with about 10 users. Nothing I'm aware of. This is built in integration with the OCS platform. -- Daniel A. Galant Imagine what we could be... if we could just imagine. "todd" <todd@discussions.microsoft.com> wrote in message news:089333D7-F0D4-4994-87E3-74A387AE33D0@microsoft.com... > I see there is a user status that works with Office Communicator. Is > there > away to...

SDK for Microsoft POS 2.0
Does anyone know of an SDK for MS POS 2.0? I have noticed several companies making add-ons for modules for MS POS, are they standalone products that integrate with the POS database or are they actually integrated into the MS POS itself? Thanks for your comments. On Nov 22, 4:46=A0am, NetKnight <NetKni...@discussions.microsoft.com> wrote: > Does anyone know of an SDK for MS POS 2.0? > Thanks for your comments. they are standalone products integrated with the database, and also add-on integrated with SO POS/SO Manager via COM antonio ...

FRx
Dear, I encountered problem of changing default base period. The version of FRx software is 6.5.28 and it is running under Windows 2K Professional. The situation was: After opened the Catalog of Report Window, there was a pop-up message box which said 'Changed the default base period to C'. I did not know why this could happen. - Would you please tell me under what circumstances this pop-up message box would display?? In order to continue to use this software, I only could click 'OK' from the message box. The report date is then changed to 'Default Base Period' - (Ja...

Import Customer Records
I am trying to import customer records and I am running into the following error: DOC 1 ERROR: Are you sure you want to delete this customer record? Ln 810 Col 2, Update module, Last CBOM field read was root.'Customer ID', Last window field visited was 'Clear Button' of window 'RM_Customer_Maintenance' of form 'RM_Customer_Maintenance' Any ideas? Thanks! Are you on the latest integration manager service pack? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at h...

divide by 0 error jmp
Hi! I have a spreadsheet where if certain cells are empty, I get a divide by 0 errors. I understand I can nopt divide by 0; that is not the issue. My issue is I can not seem to add a column of numbers if one or more have divide by 0 errors. Can someone suggest how I can either get rid of the divide/0 error, or be able to add a selection of cells where 1 or more have /0 errors? Hi the choice is your:- To sume a range with div/0 in use =SUMIF(A1:A10,"<>#DIV/0!") To eliminate div/o use =IF(ISERROR(9/C6),"",9/C6) In this case 9/c6 generates a Div/0 error Mike &qu...

GDI+ and VC 6.0
Is it possible to use GDI+ in vc 6 Environment. If so what i have to do for that what r files lib r needed to copy into my currnet project plz help me sir "srVas" <it_srini@gawab.com> wrote in message news:<#j7tsdynEHA.2612@TK2MSFTNGP15.phx.gbl>... > Is it possible to use GDI+ in vc 6 Environment. > If so what i have to do for that what r files lib r needed to copy into my > currnet project > plz help me sir Hello there, yes of course you can use GDI+ from vc6 , but only on NT/2000/XP. the runtime for GDI+ comes with windows so deployment is not an issue. ...

CRM 3.0 Upgrade. Missing notes in Cases, that were there in 1.2
Just finished my upgrade from 1.2 to 3.0 on Monday. Have been upgrading SFO clients over the last couple of days. All data came over fine, and all looks and is working great. Had a couple of users report that notes they had put in some of their Active Cases in 1.2 are no longer there. If they click the drop down arrow, the notes are in the summary. If the case is opened they are nowhere to be found. This is true in both the web/SFO client. Anyone have any ideas? Cheers, Terry I know of a limitation in v3 whereby the note title does not display in the note list view, this can be more ...

Publisher 03, how do I apply a change to all pages in a calendar?
I created a 12-page calendar - a full page per month. How can I change a font and have it apply to all 12 pages? Similarly, how can I add a graphic and have it show on all pages? Thanks! Click the font scheme, it should be on the calendar task bar, if not open it from the format menu. Select a font scheme or create one of your own. The graphic is simple enough, insert it on the first page, paste it on the next 11. It should paste exactly where it was placed on page one. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "EddieP...

How to convert PDF file to Text using VC++ 6.0
Dear Developer / Experts , I am developing an application which required to read PDF file and convert it into txt file . I dont have any idea how to do this. Please let me know if there is any free ActiveX / library to achieve this task. I am using VC++ 6.0 as my development environment . Thanks in advance , Regards , Jignesh Patel [VC++ : Software Developer] This might work for you. Since the full version requires membership this may not be enough, but it's worth giving a look. http://partners.adobe.com/public/developer/acrobat/sdk/index.html Tom <jignesh2680@gmail.com&g...

Exchange 2000 Server will be change IP address
We will change Exchange 2000 Server IP address . Which we need to reconfig? Exchange Server will be use private IP. 1. Which port require to open in firewall? 2. Which we need to reconfig in exchange? I change IP address in TCP/IP only. 1. You'll need to update the firewall to reflect a new prt 25 forwarding as well as 80/443 if you're providing OWA. 2. Exchange won't need any changes. Just do an nbtstat -R and an ipconfig /registerdns after you change the IP -- Thanks, Brian Desmond Windows Server MVP www.briandesmond.com "TONY" <TONY@discussions.micros...

Ill Ex03 server, can't open event log or change services with msc
I have an exchange 2003 server which did not come up happily after a reboot which I forced after the latest IMF update. (Dell raid 5 powerdege Hardware seems OK) I have a message saying that " a service did not start" etc. What I did was start in safe mode and try to change the service start up manual, however althogh I could view the services, I could could not right click and change properties. Also I could view the event log, but not the properties for each event. I was, however able to edit the registry to change service properities. Reboot and go in in "un-safe&quo...

Macro to close GP 10.0
Hello, I've read lots of posts on how to use a macro to close GP - which worked great for me in versions older than GP 10.0. But, I have not been able to duplicate the ability to close GP 10.0 using a macro. I've tried to create a macro where the only thing it does is close GP 10.0 and I still couldn't get it to work. Thanks in advance!! Jeffrey, In version 10, the macro command for exiting the application changed a bit. Include the following in your macro: # DEXVERSION=10.0.193.0 2 2 CommandExec form BuiLtin command cmdQuitApplication That should do it! -- MG.- Ma...

CRM 3.0
I have been struggling with getting our CRM 3.0 system customized for several weeks and this newsgroup has been an excellent source of reference. However, I'm having to use this as my primary reference resource because Microsoft has shipped CRM without much in the way of documentation. Does anyone know where you can find actual, comprehensive documentation on actually getting this product customized and set up? The Implementation Guide is weak - anyone who's read it knows that it lobs out concepts without any real "meat" to back it up with specific details on how to i...

Amount spent this month on check register stays on 0 dollar amount
My Microsoft Money 2002 program will no longer show total amounts spent for each catagory at the bottom feedback section on the check register screen. I have made no changes to the program that I am aware of. Up till now it always displayed the amount when I recorded a new withdrawal or highlighted an old entry. Hope someone can help! Thanks, Gene ...

Excel 4.0 Macro help files for Excel 2002 do not work
Either from within Excel, or double clicking on the XLMACRO.CHM file from within Windows Explorer, I always get the same screen: Download the updated help screens. I DID THAT, AND IT MAKES NO DIFFERENCE! CAN ANYONE HELP ME OUT? Have I got the wrong files, or is some switch set wrong somewhere? I've downloaded, installed and added the excel 4.0 macro help functions that are necessary, and into the correct directory. It's located with all the other help files. (C:\Program Files\Microsoft Office\Office10\1033) I've downloaded from 2 update files, and neither makes a diffe...

CRM 4.0: Problems with decimal separator
Hi! We have upgraded a system vom 3.0 to 4.0 and now we have a big problem with all money fields. After upgrading to 4.0 the decimal separator in the systems settings was set to "." and the setting for the separator for thousand was set to ".", too. We changed this to the german setting (e.g. 1.000,00) and saved the setting. Unfortunately the change does not take any effect to the system. All money fields still look like "1.000.00". The problem is, that as soon as user enters a value in the field, and saves the dataset, the system adds 4 "0" to the ...

Add menus
Hi, I trying to add a new menus in Tools|Routines|Inventory for GP 9.0 I'm using. Anyone can help? Thanks in advance. Regards, Chee SL. Chee, To add options to Dynamics GP menus you will need to create a Command form in Dexterity to open your own windows. The procedure is described in Part 5, Chapter 22 of the Microsoft Dynamics GP Integration Guide (IG.PDF) provided with the Dexterity development environment. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.bl...

Changing text of Reply Header in OL2002
I am looking for a way to change the text in the reply header in Outlook2002. Currently is adds up to 8 lines of text that are already contained within the actual header of the message itself. I do not really need 'Original Message' followed by the entirity of the mail header. A simple 'X said on Y date' format would be just fine. You used to be able to do this in older versions of Outlook by editing the reply.usa or reply.rtf files, but those seem to have gone away. ...

Why (0.09+0.01-0.1) is not equal to 0.09+0.01-0.1 ?
Hi, Does anybody know why in Excel2000 the (0.09+0.01-0.1) = -1,38778E-17 while 0.09+0.01-0.1 = 0 (as taught in schools) The same error you get with (0.09+0.01)-0.1. I checked it in on Intel Celeron 400MHz and Pentium IV with on two operation systems: W2K and W98. Seems to be Excel bug. I found it incementing a Double variable in VBA. Does anybody know how to get rid of that? Thanks in advance Valdi -- Valdi ------------------------------------------------------------------------ Valdi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26338 View this ...

CRM 3.0-SharePoint 2007 Integration
Hi all, I've been doing some very extensive research online for integration with SharePoint 2003/2007. I managed to find: 1. The CRM List Web Part from Microsoft (the one for either 2003/2007) 2. Integrating CRM data into MOSS using the BDC 3. Integrating SharePoint into CRM as a Navigation link or IFRAME app 4. Integrate CRM data into SharePoint Dashboards using the Excel Services in SharePoint Has anyone came across or have any web parts or components that can potentially integrate CRM into SharePoint? Any suggestions or comments would be gladdy accepted! :) Thank you all in adv...

Does anybody use a Mac to access RMS 2.0 on XP?
Is anybody using a Mac to access RMS 2.0 running on a Windows system? I am considering replacing one of my XP system with a Mac but need to ensure that I still can use Admin and Manager to connect to the database on a Windows XP system. Cant comment on RMS 2.0 as yet, but i run windows XP pro on my Macbook Pro, and have no problems at all running RMS SO 1.3 & Hq software and joining a Server 2003 domain, exchange server etc. If your running windows on your mac, it shouldnt be a problem. I cant see any reason why version 2.0 would be any less likely to work! Phil "Gerd&quo...

Dates appear incorrect in CRM 4.0
I wasted a fair bit of time over this problem, so thought I would post this note in case anyone else encounters the same issue. (I understand this is only an issue in certain locations that have gone off daylight saving time, including some states of Australia). Since yesterday dates (e.g. modifiedon) had appeared incorrect in CRM 4.0 - in fact they appeared as UTC date (and time) even through a time zone was set for the user. Setting non-Australian time zones displayed the correct date, however Australian time zones reverted to UTC time. I eventually discovered this is a known pro...

GAL changes not downloading to Outlook 2003 clients (cached mode)
Hi everyone, Our Exchange server is not updating the OAB with the new users that have been created over the last few months. Since we are all using Outlook 2003 with cached mode turned on, the clients looks up the OAB even when you are online. Since the client defaults to the Global Address Book, the new entries are missing from there. However, if the user chooses the All Users address list then they can see the new users there. Turn cached mode off and the GAL is 100% up to date. I've run the OABINTEG tool but I'm not sure what the results mean. Can anyone help with this? Check t...

CRM 3.0 helpdesk solution
Can anyone give me some direction in setting up an IT related CRM helpdesk. From my understanding the method would be to setup a helpdesk email address on Exchange and route all email to a helpdesk Queue in CRM. Then thru some sort of workflow have an alert sent out to all members of the IT Team who can all access the queue and self-assign their own cases based on whomever has the knowledge and time. I don't really have the CRM knowledge to pull this off on my own but perhaps with a tiny bit of guidance I can glean my way thru. Hi Twillikers, I've been looking at this for some ...