Set the line weight with VBA in Excel 07

I applied a chart style to a chart. I had the macro recorder on while I 
manipulated the chart. I saw the code to change the default size of the 
marker, but I could not get any code to appear for adjusting the line weight 
of the data series while a chart style is applied. I looked in the series 
object model and don't see a property by which to set the line weight. The 
old Border property in 03 is gone. 

How should I adjust this programatically?

Thanks.

TL
0
TroyLee (3)
3/28/2009 1:25:01 PM
excel.charting 18370 articles. 0 followers. Follow

6 Replies
418 Views

Similar Articles

[PageSpeed] 32

Hi,

Try,

activechart.SeriesCollection(1).format.line.weight=3

Cheers
Andy

TroyLee wrote:
> I applied a chart style to a chart. I had the macro recorder on while I 
> manipulated the chart. I saw the code to change the default size of the 
> marker, but I could not get any code to appear for adjusting the line weight 
> of the data series while a chart style is applied. I looked in the series 
> object model and don't see a property by which to set the line weight. The 
> old Border property in 03 is gone. 
> 
> How should I adjust this programatically?
> 
> Thanks.
> 
> TL

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
3/28/2009 1:53:13 PM
> 
> activechart.SeriesCollection(1).format.line.weight=3
> 
> Cheers
> Andy
> 

Ok Andy. Now I'm really confused. I looked at the SeriesCollections and the 
Series object. Only under the Series object does Format appear and it says 
that it is Read only. Additionally, there are no properties associated with 
the Format property according to the MSDN site. So, while the above may work 
(as I'll find it out in a moment), how is that you knew the syntax especially 
considering the macro recorder does not write this code?

Thanks for the immediate help.

TL
0
TroyLee (3)
3/28/2009 2:07:01 PM
It's not clear? Can't you speak Microsoftese?

This bothered me a great deal while I tried for months to make sense of the 
new model for formatting. Here is what you need to keep in mind:

Format is read-only. Format contains objects which are not read-only.

Andy has had the patience to do a lot of experimentation with the new object 
model, using dummy charts and the Object Browser in the VB Editor. He 
probably has tried to rely on the help files, but they are pretty unhelpful, 
even by Microsoft's standards.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"TroyLee" <TroyLee@discussions.microsoft.com> wrote in message 
news:98BAD423-D494-4742-B60E-AA9D75854CCB@microsoft.com...
>
>>
>> activechart.SeriesCollection(1).format.line.weight=3
>>
>> Cheers
>> Andy
>>
>
> Ok Andy. Now I'm really confused. I looked at the SeriesCollections and 
> the
> Series object. Only under the Series object does Format appear and it says
> that it is Read only. Additionally, there are no properties associated 
> with
> the Format property according to the MSDN site. So, while the above may 
> work
> (as I'll find it out in a moment), how is that you knew the syntax 
> especially
> considering the macro recorder does not write this code?
>
> Thanks for the immediate help.
>
> TL 


0
jonxlmvpNO (4558)
3/28/2009 2:29:57 PM

"Jon Peltier" wrote:

> It's not clear? Can't you speak Microsoftese?
> 
> This bothered me a great deal while I tried for months to make sense of the 
> new model for formatting. Here is what you need to keep in mind:
> 
> Format is read-only. Format contains objects which are not read-only.
> 
> Andy has had the patience to do a lot of experimentation with the new object 
> model, using dummy charts and the Object Browser in the VB Editor. He 
> probably has tried to rely on the help files, but they are pretty unhelpful, 
> even by Microsoft's standards.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services, Inc.
> http://PeltierTech.com/WordPress/
> _______
> 
> 
> "TroyLee" <TroyLee@discussions.microsoft.com> wrote in message 
> news:98BAD423-D494-4742-B60E-AA9D75854CCB@microsoft.com...
> >
> >>
> >> activechart.SeriesCollection(1).format.line.weight=3
> >>
> >> Cheers
> >> Andy
> >>
> >
> > Ok Andy. Now I'm really confused. I looked at the SeriesCollections and 
> > the
> > Series object. Only under the Series object does Format appear and it says
> > that it is Read only. Additionally, there are no properties associated 
> > with
> > the Format property according to the MSDN site. So, while the above may 
> > work
> > (as I'll find it out in a moment), how is that you knew the syntax 
> > especially
> > considering the macro recorder does not write this code?
> >
> > Thanks for the immediate help.
> >
> > TL 
> 
> 
 Jon,
Thanks for the insight. Where can one find these hidden, mystical properties 
if not in the Language Reference Manual? Does your website cover the 
properties/objects of the Format property?

Thanks.

Troy
0
TroyLee (3)
3/28/2009 2:54:01 PM
As Jon points out I have done some poking around :)

It is not very clear but it is discoverable.

I start by creating a Series object in order to get intellisense to 
provide me with valid items. So a small bit of code like this will 
reveal the Format object.

     Dim objSeries As Series

     Set objSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

     With objSeries.Format
         .Line.Weight = 2
     End With

This is the path I took through the help file.
In the code I highligheted SeriesCollection and pressed F1. Then 
followed this links. Some of the links are within the descriptive text 
others in the See Also section of the page.

Chart.SeriesCollection Method
(a Series object)
Series Object Members
Properties: Format
ChartFormat object
ChartFormat Object Members
Line
LineFormat object
LineFormat Object Members
Weight

Luckily Weight is one of the more obverse properties. Some are more 
hidden and other just do not exist.

Cheers
Andy

TroyLee wrote:
>>activechart.SeriesCollection(1).format.line.weight=3
>>
>>Cheers
>>Andy
>>
> 
> 
> Ok Andy. Now I'm really confused. I looked at the SeriesCollections and the 
> Series object. Only under the Series object does Format appear and it says 
> that it is Read only. Additionally, there are no properties associated with 
> the Format property according to the MSDN site. So, while the above may work 
> (as I'll find it out in a moment), how is that you knew the syntax especially 
> considering the macro recorder does not write this code?
> 
> Thanks for the immediate help.
> 
> TL

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
3/28/2009 4:06:43 PM
As Andy and I have said, it's in the Object Browser and in IntelliSense. 
Make a dummy chart with some dummy series, and just keep poking around.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"TroyLee" <TroyLee@discussions.microsoft.com> wrote in message 
news:B7793556-E627-437A-9CE0-9BE14859F6A9@microsoft.com...
>
>
> "Jon Peltier" wrote:
>
>> It's not clear? Can't you speak Microsoftese?
>>
>> This bothered me a great deal while I tried for months to make sense of 
>> the
>> new model for formatting. Here is what you need to keep in mind:
>>
>> Format is read-only. Format contains objects which are not read-only.
>>
>> Andy has had the patience to do a lot of experimentation with the new 
>> object
>> model, using dummy charts and the Object Browser in the VB Editor. He
>> probably has tried to rely on the help files, but they are pretty 
>> unhelpful,
>> even by Microsoft's standards.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services, Inc.
>> http://PeltierTech.com/WordPress/
>> _______
>>
>>
>> "TroyLee" <TroyLee@discussions.microsoft.com> wrote in message
>> news:98BAD423-D494-4742-B60E-AA9D75854CCB@microsoft.com...
>> >
>> >>
>> >> activechart.SeriesCollection(1).format.line.weight=3
>> >>
>> >> Cheers
>> >> Andy
>> >>
>> >
>> > Ok Andy. Now I'm really confused. I looked at the SeriesCollections and
>> > the
>> > Series object. Only under the Series object does Format appear and it 
>> > says
>> > that it is Read only. Additionally, there are no properties associated
>> > with
>> > the Format property according to the MSDN site. So, while the above may
>> > work
>> > (as I'll find it out in a moment), how is that you knew the syntax
>> > especially
>> > considering the macro recorder does not write this code?
>> >
>> > Thanks for the immediate help.
>> >
>> > TL
>>
>>
> Jon,
> Thanks for the insight. Where can one find these hidden, mystical 
> properties
> if not in the Language Reference Manual? Does your website cover the
> properties/objects of the Format property?
>
> Thanks.
>
> Troy 


0
jonxlmvpNO (4558)
3/29/2009 7:43:21 PM
Reply:

Similar Artilces:

How do I create an area chart in Excel
I'm trying to create a column chart in Excel with two axes representing two metircs, and the area of the column representing a third metric. How do I do this? Lizanne - I believe the links on this page will help: http://peltiertech.com/Excel/ChartsHowTo/VariableWidthColumns.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Lizanne wrote: > I'm trying to create a column chart in Excel with two axes representing two > metircs, and the area of the column representing a third metric...

Problem sending an Excel file as an attachment in Outlook Express
I can receive Excel files as Outlook Express Attachments but when I send one it changes to an .dat file and can not be opened. I use MS Office 2000. Any suggestions? Hi this is more a Outlook related question. So you may post this in the Outlook NG -- Regards Frank Kabel Frankfurt, Germany "Olgolfer" <Olgolfer@discussions.microsoft.com> schrieb im Newsbeitrag news:E98CE6AD-0649-411F-9D1D-2B93AC5C4729@microsoft.com... > I can receive Excel files as Outlook Express Attachments but when I send one > it changes to an .dat file and can not be opened. I use MS Office 2000. ...

Cursor highlights the wrong rows in excel?
I find that I have to select 3 rows above the row I want to highlight in excel. This carries forward when selecting multiple rows. My mouse is always about 3 lines above the cells being acted upon. ...

Excel Jululian 04-28-10
Good morning please help can i create an index sheet for all my excels files (XLS) regrads yes george, if thats what you want to do. RegMigrant "George A. Jululian" wrote: > Good morning > > please help > > can i create an index sheet for all my excels files (XLS) > > regrads oh, go on then two ways - build an excel macro what gets the information from windows so can (kind of) keep itself up to date. I am guessing you have no macro background so i offer the much cheaper way:- got to Start, run type "cmd" wi...

Excel remembers! How?
I type in "FRENCH"(no quatos) in cell A1 The I type in ENGLISH in cell b1 When I get back to cell a2 and type in the letter F the etxt is completed and it says FRENCH How does this happen/what is it called/how do you turn it OFF Thanks for the help Ian, It is called "AutoCompletion". To turn it off, go to the Tools menu, choose Options, then the Edit tab. There, uncheck the "Enable AutoComplete for cell values" options. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "ian&qu...

Strange Excel Problem.....
Hi there, We are facing one very strange excel problem in our organization.We have purchased new IBM thinkcenter 851 desktops for our Managers.We have installed Windows 2000 professional as operating system and Office 97 as office in that pcs.Now we are facing one very strange problem....Normally when you click on "New" shortcut( I mean right click anywhere and select "new" option),you will find "Microsoft Excel Worksheet" as shortcut listed in that option and when you select it new excel worksheet will be opened.But here,whenever i click on "new"...

How do I copy a chart made in Excel to Powerpoint?
I have created a number of charts in Excel that I need to export to Powerpoint for a presentation. I also want to turn each graph into a jpeg or pdf file for publication. How do I do this? For sharing charts in Office 2003 or earlier: Using Excel with Other Office Applications http://peltiertech.com/Excel/XL_PPT.html Office 2007 throws some wrenches into the gears. To export charts as image files: Enhanced Export Chart Procedurehttp://peltiertech.com/WordPress/2008/06/09/enhanced-export-chart-procedure/ Use PDF or GIF, which are optimized for line art, not JPG, which is optimized fo...

Excel 2007 Chart SeriesCollection(1).Interior.ColorIndex
I thought I had posted this already, but can't find it. Sorry if it's a duplicate. I have the following code that was written in Excel 2003. Every time I run it in Excel 2007 it crashes. I can't find anything about changing the colors of a series in an Excel 2007 chart. Also, when I try to record a macro to change the chart colors to gain insight, nothing gets written to the macro regarding the color change. Help! Sheets("Chart1").SeriesCollection(1).Interior.ColorIndex = 5 ...

Removal of lines
I am just a self taught beginner, so could someone please explain to me how can I remove the lines separating the columns e.g. between columns A B & C, in lines 1 and 2, so that if I have a heading on a worksheet, which is meant to incorporate more than one column, e.g. 2006, so that I could then insert the months in the next line 3. How do I retain the year 2005 numerals in the middle of the line? Thank you. Hi You could mark your range of cells, A1:C1 and Format>Cells>Alignment>Merge then choose Alignment>Horizontal>Center. Personally, I avoid merged cells where ...

Line graph question
How do I get my date axes (the horizontal axes) to start at zero followed by the first day of the month. -- WH99 Hi, If your dates are entered in the spreadsheet as dates then you may have problems - Excel stores dates as numbers so today is 39594 and 0 would be January 0, 1900. This would tend to spread the x-axis out quite a ways. If your dates are not entered as dates then make the first entry 0,0 - that is put another entry in the data area whose date value is 0 and whose value is 0. There are potential problems with this approach - each entry will be spaced equidistant apart ...

saving excel file with it's add in
Hi, I have an Excel file that uses a formula from an add in installed in m computer (*.xla). How can I save the project including the add in in order to use it i another computer that doesn't have the add in. thank -- Message posted from http://www.ExcelForum.com Do you really mean that your workbook uses a function that's in the addin? If yes, I think you have two choices. #1. Take the addin with you (and install it on the other pc). #2. Extract the code/procedure for that function from the addin and put it in your workbook. (If the addin's project is protected, then thi...

Line chart colors #2
Can someone explain where the colors for line charts are being pulled from? We have a standard template that is pulled in, but the colors are being changed and I haven't been able to figure out any logical reason for the color choices. Some are coming from our custom color palette, but others aren't in the custom, standard or theme palettes. The markers are coming out with the correct colors, though. ...

Excel Caused Error in VBE6.dll. Excel will Close
When trying to open Excel 2000 (Office Professional) / Windows ME the above message appeared and Excel would not open. Renaming the VBE6.DLL file and then repair and then reinstal Office did not work as the original dated VBE6.DLL file reappeared Has anyone had the sme problem? If so did you find a workable solution? ... and what was it? ...

excel 2007 installation
Hello: I used to have Excel XP and I install Excel 2007. Problem: I want to ger rid of the personalized features I have in XP and install Excell 2077 with the default parameters as it come from the manufacturer. Please help. Elitor Did you try just uninstalling the Excel XP version first? If so, did that not help? I've not tried this so I'd like to know! "Elitor" <Elitor@discussions.microsoft.com> wrote in message news:8C1B1E5E-0EAA-42EB-8CC2-5A228ADF8578@microsoft.com... > Hello: > I used to have Excel XP and I install Excel 2007. > Problem: I want to ...

excel spreadsheet 2003 wont show content when moved to another par
i have backed all documents which included excel spreadsheets from c drive to d drive (ANOTHER PARTITION). Now i cannot see the content of the spreadsheet when i access it from the D drive or C drive. what have i done to it? It also wont open when itry to access it from backed up USB stick. Thanks Chris I had a similar issue with some files. What happened was that somehow the windows for the files got minimized or dragged out of view somehow (not sure how this happened). Users would open a file and see nothing. This fixed it for me. Goto the Window item on toolbar, S...

How can I draw a Parabola in Excel? like y = x^2 + 3
In a1:a10 enter numbers such as 1,2,3.... In b1 type =a1^2+3 copy this down to B10 select a1:b10; click chart wizard; select XY chart done now play with values in A to get what you need best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Max" <Max@discussions.microsoft.com> wrote in message news:F72AB641-F882-4DAD-B0C3-322297A124FC@microsoft.com... > ...

Veritical & Horizontal Lines only ... Graph?
Excel2003 ... If I wish to create a Line Graph where the Line is Vertical & Horz only between datapoints (no diagonal direction) for say 30 datapoints .... How would I do this? Actually, I would like the Vertical Lines to be between datapoints & the Horz Lines to be centered over each datapoint. Is this possible? Thanks ... Kha -- Kha The code below is a user-defined function (UDF) that will produce the data you need to create the graph you want. Here's how it works: Let's say you have 15 rows of data. The UDF will create a new set of data (wherev...

Excel text to Word text
How can I copy Excel text, then paste it into MS Word without carrying over the grid lines? Any help would be appreciated. Thanks Ed English I don't believe you can copy and paste without the gridlines. However, in Word, select the data and Table>Convert>Table to Text. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Ed" <ee15803@att.net> wrote in message news:3f7e21e9.53324199@netnews.worldnet.att.net... > How can I copy Excel text, then paste it into MS Word without carrying > over the grid lines? Any help would be appreciated. Thanks &g...

Re: Excel Problem
This is a multi-part message in MIME format. ------=_NextPart_000_0046_01C5D5BA.E1DDCCD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have some sheets 1;2;3;4; ETC................. I would like the new page list release with one botton: A1-Nome de sheet(1;2;3;4; ETC) A2-Date(C50:C80) A3-Import (D50:D90) Best regards Marcello "Bob Phillips" <bob.phillips@notheretiscali.co.uk> ha scritto nel = messaggio=20 news:uk86X7v0FHA.3188@TK2MSFTNGP14.phx.gbl... > Marcello, > > I am not absolutely clear as to what you...

Excel Calculations Open in Manual Occasionally
MSOffice 2003, WinXP. When I open Excel, the Calculation Option defaults to Manual instead of remaining on Automatic. Is this a bug in Excel? I never set it to Manual. Thoughts can be posted or sent rich32822(Remove this)@hotmail.com. Regards, If the first workbook you open is set to manual it will change the setting for other workbooks as well meaning that if someone is sending you a workbook with this set, you open it by opening the attachment and Excel opens. Then you close this particular workbook and you open a new workbook, this workbook will now be set to manual -- Regards,...

How can a set an alarm in Excel?
I don't know if this is possible, but I figured I would ask: I have a spreadsheet that I work with that shows the schedules of my students; each different place my student goes has its own row, so a student may have many rows if they have many activities. currently, i auto filter the day column to the current day so i can see what students have activities on that day, and when/where they are. i was wondering if i could set an alarm on the column labeled 'start time' that would pop a window up when any value in that cell matched the system time... is this possible?? thanks, -...

Pie Chart (excel 2003)
How do I hide the 0% of the pie chart so that they only show the relevant %? If you mean on the pie then double click the data labels and choose the Number tab, choose Custom and enter the following code on the Type line on the right: 0%;[=0]""; -- Cheers, Shane Devenshire "Xueling" wrote: > How do I hide the 0% of the pie chart so that they only show the relevant %? Thanks! It does look better. But is there a way to not show the data lable of those 0% as well? "ShaneDevenshire" wrote: > If you mean on the pie then double click the data labels ...

Graphic features of EXCEL..........
Hi, I want to display the table data on Pivot GRAPH from two different sheets. How should we do that? Any advice will be great. Bill ...

Excel 2007 sorting problem
In Excel 2007, every time I change ranges, and even sometimes when I move away from a range and then come back to an old range, I have to re-enter the sort parameters every time. In Excel 2003, and for some versions before, if I sorted a selected range (say A4:Q54) by Col A Asc, Col G Asc, and Col C Desc then Excel would remember the sort parameters so that if I changed ranges to say A60:Q100 it would keep those same parameters loaded and I could just simply sort my data without having to re-enter the sort parameters all over again. Please direct me to where I can get some help on this issue...

Extra prompts from Excel 2007 after saving a CSV file
Here's the scenario: 1. I open a .CSV file in Excel 2007. 2. I make some data change to the file. 3. I save the file. Excel prompts me to use the CSV format again. This is a little annoying, but I answer Yes, I want to keep this format. 4. I try to close Excel. 5. Excel prompts me with "Do you want to save the changes you made to '(filename)?' Yes/No/Cancel. This is INFURIATING. I just saved the darn file. So I click No, thereby training myself to click no when prompted by Excel to save changes, guaranteeing I'll lose work in the future. Does anyone know a way to get E...